Contenido
Si está al menos un poco familiarizado con las expresiones regulares, entonces no necesita anunciarlas. Si no está del todo en el tema, entonces las expresiones regulares (Regular Expressions = RegExp = "regexps" = "regulars") es un lenguaje donde, usando caracteres especiales y reglas, las subcadenas necesarias se buscan en el texto, se extraen o reemplazado con otro texto. Esta es una herramienta muy poderosa y hermosa, un orden de magnitud superior a todas las demás formas de trabajar con texto.
Ya describí en detalle y con un montón de ejemplos de la vida cómo puede agregar soporte de expresiones regulares a Excel usando macros simples. Si no ha leído este artículo, le recomiendo que lo lea antes de continuar. Descubrirás muchas cosas nuevas, te lo garantizo 🙂
Sin embargo, la pregunta sigue abierta: ¿cómo agregar la capacidad de usar expresiones regulares en Power Query? Power Query, por supuesto, es bueno por sí solo y puede hacer mucho con el texto (cortar, pegar, limpiar, etc.), pero si pudiera cruzarlo con el poder de las expresiones regulares, sería solo una bomba.
Desafortunadamente, no hay funciones integradas para trabajar con RegExps en Power Query, y la ayuda y el soporte técnico oficiales de Microsoft responden negativamente a esta pregunta. Sin embargo, hay una forma de evitar esta limitación 🙂
La esencia del método.
La idea principal es simple para deshonrar.
En la lista de capacidades integradas de Power Query, hay una función Página web. La descripción de esta función en el sitio de ayuda oficial de Microsoft es extremadamente concisa:
Traducido, esto sería: "Devuelve el contenido del documento HTML desglosado en sus estructuras de componentes, así como una representación del documento completo y su cuerpo después de que se hayan eliminado las etiquetas". Descripción regular, francamente.
Normalmente esta función se utiliza a la hora de importar datos de la web y se sustituye automáticamente, por ejemplo, cuando seleccionamos en la pestaña Datos Comando Desde Internet (Datos — De la web). Le damos a la función una página web como argumento, y nos devuelve su contenido en forma de tablas, habiendo borrado previamente todas las etiquetas.
Lo que NO dice la ayuda es que además del lenguaje de marcado HTML función Página web admite secuencias de comandos de JavaScript, que ahora es omnipresente en los sitios web de Internet. ¡Y JavaScript, a su vez, siempre ha sido capaz de trabajar con expresiones regulares y tiene funciones integradas para RegExps! Entonces, para implementar expresiones regulares en Power Query, necesitaremos alimentar las funciones Web.Page como un argumento para un pequeño programa de JavaScript que hará todo el trabajo para Power Query.
Cómo se ve en JavaScript puro
Hay muchos tutoriales detallados sobre cómo trabajar con expresiones regulares en JavaScript en Internet (por ejemplo, uno, dos).
En resumen y simplificado, el código JavaScript se verá así:
Aquí:
- var str = 'Pagar facturas 123 y 789 por salchicha'; – crear una variable str y asígnale el texto fuente que vamos a analizar.
- var patrón = /d+/gi; – crear una expresión regular y ponerla en una variable patrón.
La expresión comienza con una barra inclinada (/).
La expresión misma aquí, por ejemplo, es d+ representa cualquier secuencia de dígitos.
A través de la fracción después de la expresión, hay parámetros de búsqueda adicionales (modificadores), se pueden especificar en cualquier orden:
- g – significa búsqueda global, es decir, después de encontrar una coincidencia, no debe detenerse, sino continuar la búsqueda hasta el final del texto. Si este modificador no está configurado, nuestro script solo devolverá la primera coincidencia (123)
- i – buscar sin importar mayúsculas y minúsculas
- m – búsqueda de varias líneas (se utiliza cuando el texto de origen se divide en varias líneas)
- var resultado = str.match(patrón).join(';'); – realizar una búsqueda en el texto fuente (str) por la expresión regular dada (patrón) y poner los resultados en una variable resultado, concatenándolos con un punto y coma usando el comando únete
- documento.escribir(resultado); – mostrar el contenido de la variable de resultado
También tenga en cuenta que las cadenas de texto (excluyendo las expresiones regulares) en JavaScript están encerradas entre apóstrofes, no entre comillas como en Power Query o VBA.
A la salida, este script nos dará como resultado todos los números que se encuentran en el texto fuente:
123, 789
El curso corto de JavaScript ha terminado, gracias a todos. Espero que entiendas la lógica 🙂
Queda por trasladar esta construcción a Power Query.
Función de búsqueda y extracción de texto por expresión regular en Power Query
Hacemos lo siguiente:
1. Abra Excel y cree un nuevo Power Query vacío en la pestaña Datos – Obtener datos / Crear solicitud – De otras fuentes – Solicitud vacía (Datos — Obtener datos / Nueva consulta — De otras fuentes — Consulta en blanco). Si tiene una versión anterior de Excel 2010-2013 y Power Query que no tiene integrada, pero se instaló como un complemento separado, entonces todo esto estará en la pestaña Power QueryY no Datos.
2. En la ventana vacía del editor de consultas que se abre, en el panel derecho, ingrese inmediatamente el nombre de nuestra futura función (por ejemplo, fxRegExpExtracción)
3. vamos a la pestaña Ver – Editor avanzado (Ver — Editor avanzado), borramos todo el código M de la solicitud vacía y pegamos allí el código de nuestra superfunción:
Cuida tus manos:
En la primera línea, decimos que nuestra función tendrá tres argumentos de texto: txt – el texto original que se analiza, expresiones regulares – patrón de expresión regular, delimitar — carácter delimitador para mostrar resultados.
A continuación llamamos a la función Página web, formando el código JavaScript descrito anteriormente en su argumento. Pegamos y sustituimos nuestros argumentos variables en el código.
Fragmento:
[Datos]{0}[Niños]{0}[Niños]{1}[Texto]{0}
… es necesario para “caer a través” en la tabla con los resultados que necesitamos. El caso es que la función Página web como resultado, produce varias tablas anidadas que repiten la estructura de una página web. Sin este fragmento de código M, nuestra función generaría esto:
… y tendríamos que hacer clic varias veces en la palabra Mesa, sucesivamente "cayendo" en tablas anidadas secundarias en columnas Niños:
En lugar de toda esta cita, indicamos inmediatamente en el código de nuestra función qué tabla y columna anidadas (Texto) nosotros necesitamos.
Aquí, de hecho, están todos los secretos. Queda por apretar el botón Acabado en la ventana editor avanzado, donde insertamos nuestro código, y puede continuar con lo más delicioso: pruebe nuestra función en el trabajo.
Aquí hay un par de ejemplos de semillas.
Ejemplo 1. Recuperar el número de cuenta y la fecha de la descripción del pago
Tenemos un extracto bancario con una descripción (propósito) de los pagos, donde debe extraer los números y las fechas de las facturas pagadas en columnas separadas:
Cargamos la tabla en Power Query de la forma estándar a través de Datos: de la tabla/rango (Datos — De Tcapaz/Rángel).
Luego agregamos una columna calculada con nuestra función a través de Agregar columna: llamar a la función personalizada (Agregar columna — Invocar función personalizada) e ingrese sus argumentos:
Como una expresión regular (argumento expresiones regulares) plantilla que usamos:
(d{3,5}|d{2}.d{2}.d{4})
… traducido al lenguaje humano que significa:
números de 3 a 5 dígitos (números de cuenta)
or
fragmentos de la forma “número de 2 bits – punto – número de 2 bits – punto – número de 4 bits”, es decir, fechas de la forma DD.MM.YYYY.
Como carácter delimitador (argumento delimitar) introduzca un punto y coma.
Después de hacer clic en OK nuestra función mágica analiza todos los datos iniciales de acuerdo con nuestra expresión regular y forma una columna para nosotros con los números encontrados y las fechas de las facturas:
Queda por separarlo por punto y coma usando el comando Inicio — Columna dividida — Por delimitador (Inicio — Columna dividida — Por delimitador) y conseguimos lo que queríamos:
¡Belleza!
Ejemplo 2: extraer direcciones de correo electrónico del texto
Supongamos que tenemos la siguiente tabla como datos iniciales:
… de donde necesitamos extraer las direcciones de correo electrónico que se encuentran allí (para mayor claridad, las resalté en rojo en el texto).
Como en el ejemplo anterior, cargamos la tabla en Power Query de forma estándar a través de Datos: de la tabla/rango (Datos — De Tcapaz/Rángel).
Luego agregamos una columna calculada con nuestra función a través de Agregar columna: llamar a la función personalizada (Agregar columna — Invocar función personalizada) e ingrese sus argumentos:
Analizar direcciones de correo electrónico es una tarea más difícil y hay un montón de expresiones regulares de diversos grados de pesadilla para resolverlo. Utilicé una de las opciones simples, no ideal, pero bastante funcional en la mayoría de los casos:
[w|.|-]*@w*.[w|.]*
Como separador (delimitar) puede introducir un punto y coma y un espacio.
Haga clic en OK y obtenemos una columna con direcciones de correo electrónico extraídas del texto original “porridge”:
¡Magia!
PS
Como dice el refrán: “No hay cosa tan buena que no se pueda hacer aún mejor”. Power Query es genial por sí solo, y cuando se combina con expresiones regulares, nos brinda un poder y una flexibilidad completamente irreales para procesar cualquier dato de texto. Espero que Microsoft algún día agregue compatibilidad con RegExp en las actualizaciones de Power Query y Power BI y todos los bailes anteriores con una pandereta se conviertan en cosa del pasado. Bueno, por ahora, sí.
También quiero agregar que es conveniente jugar con expresiones regulares en el sitio https://regexr.com/, directamente en el editor en línea. Allí en la sección Patrones comunitarios Hay una gran cantidad de temporadas regulares preparadas para todas las ocasiones. Experimente: ¡todo el poder de las expresiones regulares ahora está a su servicio en Power Query!
- Qué son las expresiones regulares (RegExp) y cómo usarlas en Excel
- Búsqueda de texto difuso en Power Query
- Ensamblar tablas de diferentes archivos usando Power Query