contingut
Si esteu almenys una mica familiaritzat amb les expressions regulars, no cal que les anuncieu. Si no esteu del tot en el tema, aleshores les expressions regulars (Expressions regulars = RegExp = “regexps” = “regulars”) és un llenguatge on, mitjançant caràcters i regles especials, es cerquen les subcadenes necessàries al text, s'extreuen. o substituït per un altre text. Aquesta és una eina molt potent i bonica, un ordre de magnitud superior a totes les altres maneres de treballar amb text.
Ja he descrit amb detall i amb un munt d'exemples de la vida com podeu afegir suport d'expressió regular a Excel mitjançant macros senzilles; si no heu llegit aquest article, us recomano molt que el llegiu abans de continuar. Descobriràs moltes coses noves, t'asseguro 🙂
Tanmateix, la pregunta continua oberta: com afegir la possibilitat d'utilitzar expressions regulars a Power Query? El Power Query, per descomptat, és bo per si sol i pot fer molt amb el text (tallar, enganxar, netejar, etc.), però si el poguessis creuar amb el poder de les expressions regulars, seria només una bomba.
Malauradament, no hi ha funcions integrades per treballar amb RegExps a Power Query, i l'ajuda i el suport tècnic oficials de Microsoft responen a aquesta pregunta de manera negativa. Tanmateix, hi ha una manera d'evitar aquesta limitació 🙂
L’essència del mètode
La idea principal és fàcil de deshonrar.
A la llista de capacitats integrades de Power Query, hi ha una funció Pàgina web. La descripció d'aquesta funció al lloc d'ajuda oficial de Microsoft és extremadament concisa:
Traduït, això seria: "Retorna el contingut del document HTML desglossat en les seves estructures components, així com una representació del document complet i el seu cos després d'haver eliminat les etiquetes". Tan tal descripció, francament.
Normalment aquesta funció s'utilitza quan s'importen dades del web i es substitueix automàticament, per exemple, quan seleccionem a la pestanya dades Comando Des d'Internet (Dades — Del web). Donem a la funció una pàgina web com a argument, i ens retorna el seu contingut en forma de taules, havent esborrat prèviament totes les etiquetes.
El que l'ajuda NO diu és que a més del llenguatge de marques HTML function Pàgina web admet scripts JavaScript, que ara és omnipresent als llocs web d'Internet. I JavaScript, al seu torn, sempre ha estat capaç de treballar amb expressions regulars i té funcions integrades per a RegExps! Per tant, per implementar expressions regulars a Power Query, haurem d'alimentar les funcions Web.Page com a argument d'un petit programa JavaScript que farà tot el treball per a Power Query.
Com es veu en JavaScript pur
Hi ha molts tutorials detallats sobre com treballar amb expressions regulars en JavaScript a Internet (per exemple, un, dos).
En resum i simplificat, el codi JavaScript tindrà aquest aspecte:
Aquí:
- var str = 'Paga les factures 123 i 789 per l'embotit'; - Crear una variable str i assigneu-li el text font que analitzarem.
- var patró = /d+/gi; – crear una expressió regular i posar-la en una variable patró.
L'expressió comença amb una barra inclinada (/).
L'expressió mateixa aquí, per exemple, és d+ representa qualsevol seqüència de dígits.
A través de la fracció després de l'expressió, hi ha paràmetres de cerca addicionals (modificadors) que es poden especificar en qualsevol ordre:
- g – significa cerca global, és a dir, després de trobar una coincidència, no s'ha d'aturar, sinó continuar la cerca fins al final del text. Si aquest modificador no està establert, el nostre script només retornarà la primera coincidència (123)
- i – cerca sense tenir en compte el cas de les lletres
- m – cerca multilínia (s'utilitza quan el text font es divideix en diverses línies)
- var resultat = str.match(patró).join(';'); – fer una cerca al text font (str) per l'expressió regular donada (patró) i posa els resultats en una variable resultat, concatenant-los amb un punt i coma mitjançant l'ordre unir-se
- document.write(resultat); – mostrar el contingut de la variable de resultat
Tingueu en compte també que les cadenes de text (excloent les expressions regulars) a JavaScript s'inclouen entre apòstrofs, no entre cometes, ja que es troben a Power Query o VBA.
A la sortida, aquest script ens donarà com a resultat tots els números que es troben al text font:
123, 789
El curs curt de JavaScript s'ha acabat, gràcies a tots. Espero que entenguis la lògica 🙂
Queda per transferir aquesta construcció a Power Query.
Funció de cerca i extracció de text per expressió regular a Power Query
Fem el següent:
1. Obriu Excel i creeu una nova Power Query buida a la pestanya Dades – Obtenir dades / Crear sol·licitud – Des d'altres fonts – Sol·licitud buida (Dades — Obtenir dades / Nova consulta — D'altres fonts — Consulta en blanc). Si teniu una versió antiga d'Excel 2010-2013 i Power Query que no teniu incorporada, però s'ha instal·lat com a complement independent, tot això estarà a la pestanya Power ConsultaI no dades.
2. A la finestra buida de l'editor de consultes que s'obre, al panell dret, introduïu immediatament el nom de la nostra futura funció (per exemple, fxRegExpExtract)
3. Anem a la pestanya Visualització: Editor avançat (Veure — Editor avançat), esborram tot el codi M de la sol·licitud buida i hi enganxem el codi de la nostra superfunció:
Vigileu les vostres mans:
A la primera línia, diem que la nostra funció tindrà tres arguments de text: txt - el text original que s'està analitzant, expressió regular - Patró d'expressió regular, delim — caràcter delimitador per mostrar els resultats.
A continuació anomenem la funció Pàgina web, formant el codi JavaScript descrit anteriorment al seu argument. Enganxem i substituïm els nostres arguments variables al codi.
Fragment:
[Dades]{0}[Nens]{0}[Nens]{1}[Text]{0}
... cal "caure" a la taula amb els resultats que necessitem. La qüestió és que la funció Pàgina web com a resultat, produeix diverses taules imbricades que repeteixen l'estructura d'una pàgina web. Sense aquesta peça de codi M, la nostra funció sortiria això:
… i hauríem de fer clic a la paraula diverses vegades Taula, "entrant" successivament en taules niuades en columnes nens:
En lloc de tota aquesta cita, indiquem immediatament al codi de la nostra funció quina taula i columna imbricades (Text) necessitem.
Aquí, de fet, tots els secrets. Queda per prémer el botó Finish a la finestra editor avançat, on hem inserit el nostre codi, i podeu procedir al més deliciós: proveu la nostra funció a la feina.
Aquí teniu un parell d'exemples de llavors.
Exemple 1. Recuperació del número de compte i la data de la descripció del pagament
Tenim un extracte bancari amb una descripció (propòsit) dels pagaments, on heu de treure els números i les dates de les factures pagades en columnes separades:
Carreguem la taula a Power Query de la manera estàndard Dades: de la taula/interval (Dades: de Tcapaç/Ràngel).
A continuació, afegim una columna calculada amb la nostra funció via Afegeix una columna: truca a la funció personalitzada (Afegeix una columna: invoca la funció personalitzada) i introduïu els seus arguments:
Com a expressió regular (argument expressió regular) plantilla que fem servir:
(d{3,5}|d{2}.d{2}.d{4})
... traduït al llenguatge humà que significa:
números de 3 a 5 dígits (números de compte)
or
fragments de la forma "número de 2 bits - punt - nombre de 2 bits - punt - nombre de 4 bits", és a dir, dates del formulari DD.MM.AAAA.
Com a caràcter delimitador (argument delim) introduïu un punt i coma.
Després de fer clic OK la nostra funció màgica analitza totes les dades inicials segons la nostra expressió regular i ens forma una columna amb els números i dates trobats de les factures:
Queda per separar-lo per punt i coma mitjançant l'ordre Inici — Dividir la columna — Per delimitador (Inici — Columna dividida — Per delimitador) i aconseguim el que volíem:
Bellesa!
Exemple 2: extreu adreces de correu electrònic del text
Suposem que tenim la taula següent com a dades inicials:
... d'on hem de treure les adreces de correu electrònic que hi trobem (per a més claredat, les vaig destacar en vermell al text).
Com a l'exemple anterior, carreguem la taula a Power Query de la manera estàndard via Dades: de la taula/interval (Dades: de Tcapaç/Ràngel).
A continuació, afegim una columna calculada amb la nostra funció via Afegeix una columna: truca a la funció personalitzada (Afegeix una columna: invoca la funció personalitzada) i introduïu els seus arguments:
Analitzar adreces de correu electrònic és una tasca més difícil i hi ha un munt d'expressions regulars de diferents graus de malson per resoldre'l. Vaig utilitzar una de les opcions senzilles, no és ideal, però funciona bastant en la majoria dels casos:
[w|.|-]*@w*.[w|.]*
Com a separador (delim) podeu introduir un punt i coma i un espai.
Fer clic a OK i obtenim una columna amb adreces de correu electrònic extretes del text original “farinetes”:
Màgia!
PS
Com diu la dita: "No hi ha res tan bo que no es pugui millorar encara". Power Query és fantàstic per si sol i, quan es combina amb expressions regulars, ens ofereix una potència i una flexibilitat completament irreals per processar qualsevol dada de text. Espero que Microsoft afegeixi algun dia el suport de RegExp a les actualitzacions de Power Query i Power BI i que tots els balls anteriors amb un tamborí esdevinguin cosa del passat. Bé, de moment, sí.
També vull afegir que és convenient jugar amb expressions regulars al lloc https://regexr.com/, directament a l'editor en línia. Allà a la secció Patrons comunitaris Hi ha un gran nombre de temporades regulars preparades per a totes les ocasions. Experimenta: tot el poder de les expressions regulars està al teu servei a Power Query!
- Què són les expressions regulars (RegExp) i com utilitzar-les a Excel
- Cerca de text difusa a Power Query
- Muntatge de taules a partir de diferents fitxers mitjançant Power Query