Contenuti
Se hai almeno una certa familiarità con le espressioni regolari, non è necessario pubblicizzarle. Se non sei abbastanza nell'argomento, le espressioni regolari (Regular Expressions = RegExp = “regexps” = “regulars”) è un linguaggio in cui, usando caratteri e regole speciali, le sottostringhe necessarie vengono cercate nel testo, vengono estratte o sostituito con altro testo. Questo è uno strumento molto potente e bello, un ordine di grandezza superiore a tutti gli altri modi di lavorare con il testo.
Ho già descritto in dettaglio e con una serie di esempi dal vero come aggiungere il supporto per le espressioni regolari a Excel utilizzando semplici macro: se non hai letto questo articolo, ti consiglio vivamente di leggerlo prima di continuare. Scoprirai tante cose nuove, te lo garantisco 🙂
Tuttavia, la domanda rimane aperta: come aggiungere la possibilità di utilizzare espressioni regolari in Power Query? Power Query, ovviamente, è buono da solo e può fare molto con il testo (taglio, incollaggio, pulizia, ecc.), ma se potessi incrociarlo con il potere delle espressioni regolari, sarebbe solo una bomba.
Sfortunatamente, non ci sono funzioni integrate per lavorare con RegExps in Power Query e la guida e il supporto tecnico Microsoft ufficiali rispondono a questa domanda in modo negativo. Tuttavia, c'è un modo per aggirare questa limitazione 🙂
L'essenza del metodo
L'idea principale è semplice da disonore.
Nell'elenco delle funzionalità di Power Query integrate è presente una funzione Pagina web. La descrizione di questa funzione sul sito ufficiale della guida di Microsoft è estremamente concisa:
Tradotto, questo sarebbe: "Restituisce il contenuto del documento HTML suddiviso nelle sue strutture componenti, nonché una rappresentazione del documento completo e del suo corpo dopo che i tag sono stati rimossi". Descrizione così così, francamente.
Solitamente questa funzione viene utilizzata durante l'importazione di dati dal web e viene automaticamente sostituita, ad esempio, quando selezioniamo nella scheda Dati Comando Da Internet (Dati — Dal web). Diamo alla funzione una pagina web come argomento e ci restituisce il contenuto sotto forma di tabelle, dopo aver precedentemente cancellato tutti i tag.
Quello che l'aiuto NON dice è che oltre al linguaggio di markup HTML function Pagina web supporta gli script JavaScript, che ora è onnipresente sui siti Web su Internet. E JavaScript, a sua volta, è sempre stato in grado di lavorare con le espressioni regolari e ha funzioni integrate per RegExps! Quindi, per implementare le espressioni regolari in Power Query, dovremo alimentare le funzioni Web.Page come argomento per un piccolo programma JavaScript che farà tutto il lavoro per Power Query.
Come appare in puro JavaScript
Ci sono molti tutorial dettagliati su come lavorare con le espressioni regolari in JavaScript su Internet (ad esempio, uno, due).
In breve e semplificato, il codice JavaScript sarà simile a questo:
Qui:
- var str = 'Paga le bollette 123 e 789 per la salsiccia'; – creare una variabile str e assegnargli il testo sorgente che analizzeremo.
- var modello = /d+/gi; – crea un'espressione regolare e inseriscila in una variabile modello.
L'espressione inizia con una barra (/).
L'espressione stessa qui, ad esempio, è d+ sta per qualsiasi sequenza di cifre.
Attraverso la frazione dopo l'espressione, ci sono parametri di ricerca aggiuntivi (modificatori) - possono essere specificati in qualsiasi ordine:
- g – significa ricerca globale, cioè dopo aver trovato una corrispondenza, non dovresti fermarti, ma continuare la ricerca fino alla fine del testo. Se questo modificatore non è impostato, il nostro script restituirà solo la prima corrispondenza (123)
- i – ricerca indipendentemente dal caso delle lettere
- m – ricerca su più righe (usata quando il testo sorgente è diviso in più righe)
- var risultato = str.match(pattern).join(';'); – eseguire una ricerca nel testo sorgente (str) dall'espressione regolare data (modello) e inserire i risultati in una variabile colpevole, concatenandoli con un punto e virgola utilizzando il comando join
- document.write(risultato); – visualizzare il contenuto della variabile di risultato
Si noti inoltre che le stringhe di testo (escluse le espressioni regolari) in JavaScript sono racchiuse tra apostrofi, non tra virgolette come in Power Query o VBA.
In output, questo script ci darà come risultato tutti i numeri trovati nel testo sorgente:
123, 789
Il corso breve JavaScript è terminato, grazie a tutti. Spero che tu abbia la logica 🙂
Resta da trasferire questa costruzione a Power Query.
Cerca ed estrai la funzione di testo in base all'espressione regolare in Power Query
Facciamo quanto segue:
1. Apri Excel e crea una nuova Power Query vuota nella scheda Dati – Ottieni dati / Crea richiesta – Da altre fonti – Richiesta vuota (Dati — Ottieni dati/Nuova query — Da altre fonti — Query vuota). Se hai una versione precedente di Excel 2010-2013 e Power Query non hai il built-in, ma è stato installato come componente aggiuntivo separato, tutto questo sarà nella scheda Power QueryE non Dati.
2. Nella finestra vuota dell'editor di query che si apre, nel pannello di destra, inserisci subito il nome della nostra futura funzione (ad esempio, fxRegExpExtract)
3. Andiamo alla scheda Visualizza – Editor avanzato (Visualizza — Editor avanzato), cancelliamo l'intero codice M della richiesta vuota e vi incolliamo il codice della nostra superfunzione:
Guarda le tue mani:
Nella prima riga, diciamo che la nostra funzione avrà tre argomenti di testo: txt – il testo originale in analisi, regex – modello di espressione regolare, delim — carattere delimitatore per la visualizzazione dei risultati.
Quindi chiamiamo la funzione Pagina web, formando il codice JavaScript descritto sopra nel suo argomento. Incolliamo e sostituiamo i nostri argomenti variabili nel codice.
Frammento:
[Dati]{0}[Bambini]{0}[Bambini]{1}[Testo]{0}
… è necessario per “cadere” nella tabella con i risultati di cui abbiamo bisogno. Il punto è che la funzione Pagina web di conseguenza, produce diverse tabelle annidate che ripetono la struttura di una pagina web. Senza questo pezzo di codice M, la nostra funzione produrrebbe questo:
... e dovremmo fare clic sulla parola più volte Table, successivamente "cadendo" nelle tabelle nidificate figlio nelle colonne Bambini:
Invece di tutte queste citazioni, indichiamo subito nel codice della nostra funzione quale tabella e colonna nidificate (Testo) abbiamo bisogno.
Ecco, infatti, tutti i segreti. Resta da premere il pulsante Fine nella finestra editor avanzato, dove abbiamo inserito il nostro codice, e puoi procedere al più delizioso: prova la nostra funzione al lavoro.
Ecco un paio di esempi di semi.
Esempio 1. Recupero del numero di conto e della data dalla causale del pagamento
Abbiamo un estratto conto con una descrizione (scopo) dei pagamenti, in cui è necessario estrarre i numeri e le date delle fatture pagate in colonne separate:
Carichiamo la tabella in Power Query nel modo standard Dati: da tabella/intervallo (Dati — Da Tcapace/Rangelo).
Quindi aggiungiamo una colonna calcolata con la nostra funzione tramite Aggiungi colonna: chiama la funzione personalizzata (Aggiungi colonna: richiama la funzione personalizzata) e inserisci i suoi argomenti:
Come espressione regolare (argomento regex) modello che utilizziamo:
(d{3,5}|d{2}.d{2}.d{4})
… tradotto in linguaggio umano significa:
numeri da 3 a 5 cifre (numeri di conto)
or
frammenti della forma “numero a 2 bit – punto – numero a 2 bit – punto – numero a 4 bit”, ovvero date nella forma GG.MM.AAAA.
Come carattere delimitatore (argomentazione delim) inserire un punto e virgola.
Dopo aver fatto clic su OK la nostra funzione magica analizza tutti i dati iniziali secondo la nostra espressione regolare e forma per noi una colonna con i numeri trovati e le date delle fatture:
Resta da separare da punto e virgola usando il comando Home — Colonna divisa — Per delimitatore (Home — Colonna divisa — Per delimitatore) e otteniamo ciò che volevamo:
Bellezza!
Esempio 2: estrarre gli indirizzi e-mail dal testo
Supponiamo di avere la seguente tabella come dati iniziali:
…da dove dobbiamo estrarre gli indirizzi email che trovi lì (per chiarezza li ho evidenziati in rosso nel testo).
Come nell'esempio precedente, carichiamo la tabella in Power Query in modo standard tramite Dati: da tabella/intervallo (Dati — Da Tcapace/Rangelo).
Quindi aggiungiamo una colonna calcolata con la nostra funzione tramite Aggiungi colonna: chiama la funzione personalizzata (Aggiungi colonna: richiama la funzione personalizzata) e inserisci i suoi argomenti:
L'analisi degli indirizzi e-mail è un compito più difficile e ci sono un sacco di espressioni regolari di vari gradi di incubo per risolverlo. Ho usato una delle semplici opzioni, non l'ideale, ma abbastanza funzionante nella maggior parte dei casi:
[w|.|-]*@w*.[w|.]*
Come separatore (delim) puoi inserire un punto e virgola e uno spazio.
Fare clic su OK e otteniamo una colonna con gli indirizzi e-mail estratti dal testo originale “porridge”:
Magia!
PS
Come si suol dire: "Non esiste una cosa così buona che non possa essere resa ancora migliore". Power Query è interessante da solo e, se combinato con le espressioni regolari, ci offre potenza e flessibilità completamente irrealistiche nell'elaborazione di qualsiasi dato di testo. Spero che un giorno Microsoft aggiungerà il supporto RegExp negli aggiornamenti di Power Query e Power BI e tutti i balli di cui sopra con un tamburello diventeranno un ricordo del passato. Bene, per ora, sì.
Voglio anche aggiungere che è conveniente giocare con le espressioni regolari sul sito https://regexr.com/ – direttamente nell'editor online. Lì nella sezione Modelli comunitari Ci sono un numero enorme di stagioni regolari già pronte per tutte le occasioni. Sperimenta: tutta la potenza delle espressioni regolari è ora al tuo servizio in Power Query!
- Cosa sono le espressioni regolari (RegExp) e come usarle in Excel
- Ricerca di testo sfocato in Power Query
- Assemblaggio di tabelle da file diversi tramite Power Query