Contidos
Se estás polo menos un pouco familiarizado coas expresións regulares, non necesitas anuncialas. Se non estás moi ben no tema, entón as expresións regulares (Expresións regulares = RegExp = “regexps” = “regulares”) é unha linguaxe na que, mediante caracteres e regras especiais, búscanse no texto as subcadeas necesarias, extráense. ou substituído por outro texto. Esta é unha ferramenta moi poderosa e fermosa, unha orde de magnitude superior a todas as outras formas de traballar co texto.
Xa describín en detalle e cunha morea de exemplos da vida como pode engadir soporte de expresións regulares a Excel usando macros simples; se non leu este artigo, recoméndolle encarecidamente que o lea antes de continuar. Descubrirás moitas cousas novas, garantizo 🙂
Non obstante, a pregunta segue aberta: como engadir a posibilidade de usar expresións regulares en Power Query? Power Query, por suposto, é bo por si só e pode facer moito co texto (cortar, pegar, limpar, etc.), pero se puideses cruzalo co poder das expresións regulares, sería só unha bomba.
Desafortunadamente, non hai funcións integradas para traballar con RegExps en Power Query, e a axuda oficial e o soporte técnico de Microsoft responden negativamente a esta pregunta. Non obstante, hai unha forma de evitar esta limitación 🙂
A esencia do método
A idea principal é sinxela de deshonrar.
Na lista de capacidades integradas de Power Query, hai unha función Páxina web. A descrición desta función no sitio oficial de axuda de Microsoft é moi concisa:
Traducido, isto sería: "Devolve o contido do documento HTML desglosado nas súas estruturas compoñentes, así como unha representación do documento completo e do seu corpo despois de eliminar as etiquetas". Descrición así, francamente.
Normalmente esta función utilízase ao importar datos da web e substitúese automaticamente, por exemplo, cando seleccionamos na pestana Data Mando Desde Internet (Datos: da web). Dámoslle á función unha páxina web como argumento, e esta devólvenos o seu contido en forma de táboas, tendo borrado previamente todas as etiquetas.
O que NON di a axuda é que ademais da linguaxe de marcado HTML función Páxina web admite scripts JavaScript, que agora é omnipresente nos sitios web de Internet. E JavaScript, á súa vez, sempre foi capaz de traballar con expresións regulares e ten funcións integradas para RegExps. Polo tanto, para implementar expresións regulares en Power Query, necesitaremos alimentar as funcións Web.Page como argumento para un pequeno programa JavaScript que fará todo o traballo para Power Query.
Como se ve en JavaScript puro
Hai moitos titoriais detallados sobre o traballo con expresións regulares en JavaScript en Internet (por exemplo, un, dous).
En resumo e simplificado, o código JavaScript terá o seguinte aspecto:
Aquí:
- var str = 'Pagar as facturas 123 e 789 por salchicha'; - Crear unha variable str e asígnalle o texto fonte que analizaremos.
- patrón var = /d+/gi; – Crear unha expresión regular e colocala nunha variable estándar.
A expresión comeza cunha barra (/).
A propia expresión aquí, por exemplo, é d+ representa calquera secuencia de díxitos.
A través da fracción despois da expresión, hai parámetros de busca adicionais (modificadores), podendo especificarse en calquera orde:
- g – significa busca global, é dicir, despois de atopar unha coincidencia, non debes parar, senón continuar a busca ata o final do texto. Se non se define este modificador, o noso script só devolverá a primeira coincidencia (123)
- i – buscar sen ter en conta o caso das letras
- m - busca multiliña (usada cando o texto fonte está dividido en varias liñas)
- var resultado = str.match(pattern).join(';'); – realizar unha busca no texto de orixe (str) pola expresión regular dada (estándar) e pon os resultados nunha variable resultar, concatenándoos cun punto e coma mediante o comando unirse
- documento.escribir(resultado); – mostrar o contido da variable de resultado
Teña en conta tamén que as cadeas de texto (excluídas as expresións regulares) en JavaScript están encerradas entre apóstrofos, non entre comiñas como están en Power Query ou VBA.
Na saída, este script daranos como resultado todos os números atopados no texto fonte:
123, 789
O curso curto de JavaScript rematou, grazas a todos. Espero que entendas a lóxica 🙂
Queda por transferir esta construción a Power Query.
Función de busca e extracción de texto por expresión regular en Power Query
Facemos o seguinte:
1. Abre Excel e crea unha nova Power Query baleira na pestana Datos - Obter datos / Crear solicitude - Desde outras fontes - Solicitude baleira (Datos — Obter datos / Nova consulta — Desde outras fontes — Consulta en branco). Se tes unha versión antiga de Excel 2010-2013 e Power Query que non tes incorporada, pero que se instalou como un complemento separado, todo isto estará na pestana Consulta de enerxíaE non Data.
2. Na xanela baleira do editor de consultas que se abre, no panel dereito, introduza inmediatamente o nome da nosa futura función (por exemplo, fxRegExpExtract)
3. Imos á pestana Ver - Editor avanzado (Ver — Editor avanzado), borramos todo o código M da solicitude baleira e pegamos alí o código da nosa superfunción:
Coidado coas mans:
Na primeira liña, dicimos que a nosa función terá tres argumentos de texto: txt - o texto orixinal que se está a analizar, rexex - patrón de expresión regular, delimitar — carácter delimitador para mostrar os resultados.
A continuación chamamos á función Páxina web, formando o código JavaScript descrito anteriormente no seu argumento. Pegamos e substituímos os nosos argumentos variables no código.
Fragmento:
[Datos]{0}[Nenos]{0}[Nenos]{1}[Texto]{0}
... é necesario para "caer" na táboa cos resultados que necesitamos. A cuestión é que a función Páxina web como resultado, produce varias táboas aniñadas que repiten a estrutura dunha páxina web. Sen esta peza de código M, a nosa función produciría isto:
… e teriamos que facer clic na palabra varias veces Táboa, "caendo" sucesivamente en táboas aniñadas fillas en columnas Nenos:
En lugar de toda esta cita, indicamos inmediatamente no código da nosa función que táboa e columna aniñadas (Texto) necesitamos.
Aquí, de feito, están todos os segredos. Queda por premer o botón Rematar na fiestra editor avanzado, onde inserimos o noso código, e podes pasar ao máis delicioso: proba a nosa función no traballo.
Aquí tes un par de exemplos de sementes.
Exemplo 1. Recuperación do número de conta e da data da descrición do pagamento
Temos un extracto bancario cunha descrición (finalidade) dos pagos, onde debes sacar os números e as datas das facturas pagadas en columnas separadas:
Cargamos a táboa en Power Query do xeito estándar Datos: da táboa/rango (Datos: de Tcapaz/Ranxo).
Despois engadimos unha columna calculada coa nosa función via Engadir columna - Chamar a función personalizada (Engadir columna — Invocar función personalizada) e introduce os seus argumentos:
Como expresión regular (argumento rexex) modelo que usamos:
(d{3,5}|d{2}.d{2}.d{4})
... traducido á linguaxe humana que significa:
números de 3 a 5 díxitos (números de conta)
or
fragmentos da forma "número de 2 bits - punto - número de 2 bits - punto - número de 4 bits", é dicir, datas do formulario DD.MM.AAAA.
Como carácter delimitador (argumento delimitar) introduza un punto e coma.
Despois de premer OK a nosa función máxica analiza todos os datos iniciais segundo a nosa expresión regular e forma unha columna para nós cos números e datas atopados das facturas:
Queda separalo por punto e coma usando o comando Inicio — Dividir columna — Por delimitador (Inicio — Dividir columna — Por delimitador) e conseguimos o que queriamos:
Beleza!
Exemplo 2: extrae enderezos de correo electrónico do texto
Supoñamos que temos a seguinte táboa como datos iniciais:
… de onde temos que sacar os enderezos de correo electrónico que se atopan alí (para que quede claro, destaqueinos en vermello no texto).
Como no exemplo anterior, cargamos a táboa en Power Query do xeito estándar vía Datos: da táboa/rango (Datos: de Tcapaz/Ranxo).
Despois engadimos unha columna calculada coa nosa función via Engadir columna - Chamar a función personalizada (Engadir columna — Invocar función personalizada) e introduce os seus argumentos:
Analizar enderezos de correo electrónico é unha tarefa máis difícil e hai unha morea de expresións regulares de diferentes graos de pesadelo para resolvelo. Usei unha das opcións sinxelas, non é a ideal, pero funciona bastante na maioría dos casos:
[w|.|-]*@w*.[w|.]*
Como separador (delimitar) pode introducir un punto e coma e un espazo.
Prema en OK e obtemos unha columna con enderezos de correo electrónico extraídos do texto orixinal “gachas”:
¡Maxia!
PS
Como di o refrán: "Non hai cousa tan boa que non se poida mellorar aínda". Power Query é xenial por si só, e cando se combina con expresións regulares, ofrécenos un poder e unha flexibilidade completamente irreais para procesar calquera dato de texto. Espero que Microsoft engade algún día soporte RegExp nas actualizacións de Power Query e Power BI e que todos os bailes anteriores cunha pandeireta sexan cousa do pasado. Pois de momento, si.
Tamén quero engadir que é conveniente xogar con expresións regulares no sitio https://regexr.com/, directamente no editor en liña. Alí na sección Patróns comunitarios Hai un gran número de tempadas regulares preparadas para todas as ocasións. Experimenta: todo o poder das expresións regulares está agora ao teu servizo en Power Query.
- Que son as expresións regulares (RegExp) e como usalas en Excel
- Busca de texto difuso en Power Query
- Montaxe de táboas a partir de diferentes ficheiros mediante Power Query