Contenido
Uno de los videos más vistos en mi canal de YouTube es un video sobre Flash Fill en Microsoft Excel. La esencia de esta herramienta es que si necesita transformar de alguna manera sus datos de origen, solo necesita comenzar a escribir el resultado que desea obtener en la columna adyacente. Después de varias celdas escritas manualmente (generalmente 2-3 son suficientes), Excel "comprenderá" la lógica de las transformaciones que necesita y continuará automáticamente con lo que ha escrito, completando todo el trabajo monótono por usted:
La quintaesencia de la eficiencia. El botón mágico "hazlo bien" que todos amamos tanto, ¿verdad?
De hecho, hay un análogo de dicha herramienta en Power Query: allí se llama Columna de ejemplos (Columna de Ejemplos). De hecho, esta es una pequeña inteligencia artificial integrada en Power Query que puede aprender rápidamente de sus datos y luego transformarlos. Echemos un vistazo más de cerca a sus capacidades en varios escenarios prácticos para comprender dónde nos puede ser útil en tareas reales.
Ejemplo 1. Pegar/cortar texto
Digamos que tenemos una tabla tan "inteligente" en Excel con datos sobre los empleados:
Cárguelo en Power Query de la manera estándar: con el botón Desde tabla/rango de la pestaña. Datos (Datos: de la tabla/rango).
Supongamos que necesitamos agregar una columna con apellidos e iniciales para cada empleado (Ivanov SV para el primer empleado, etc.). Para resolver este problema, puede utilizar uno de dos métodos:
- haga clic con el botón derecho en el encabezado de la columna con los datos de origen y seleccione el comando Agregar columna de ejemplos (Añadir columna de ejemplos);
- seleccione una o más columnas con datos y en la pestaña Agregar una columna elige un equipo Columna de ejemplos. Aquí, en la lista desplegable, puede especificar si se deben analizar todas las columnas o solo las seleccionadas.
Luego, todo es simple: en la columna que aparece a la derecha, comenzamos a ingresar ejemplos de los resultados deseados, y la inteligencia artificial integrada en Power Query intenta comprender nuestra lógica de transformación y continuar por su cuenta:
Por cierto, puede ingresar las opciones correctas en cualquier celda de esta columna, es decir, no necesariamente de arriba hacia abajo y en una fila. Además, puede agregar o eliminar fácilmente columnas del análisis más adelante usando las casillas de verificación en la barra de título.
Preste atención a la fórmula en la parte superior de la ventana: esto es lo que crea Power Query inteligente para obtener los resultados que necesitamos. Esta, por cierto, es la diferencia fundamental entre esta herramienta y relleno instantáneo en Excel. El llenado instantáneo funciona como una "caja negra": no nos muestran la lógica de las transformaciones, sino que simplemente brindan resultados listos para usar y los damos por sentados. Aquí todo es transparente y siempre se puede entender absolutamente claramente qué está sucediendo exactamente con los datos.
Si ve que Power Query "captó la idea", entonces puede presionar el botón de manera segura OK o atajo de teclado Ctrl+Participar – Se creará una columna personalizada con una fórmula inventada por Power Query. Por cierto, luego se puede editar fácilmente como una columna normal creada manualmente (con el comando Agregar una columna: columna personalizada) haciendo clic en el ícono de ajustes a la derecha del nombre del paso:
Ejemplo 2: Caso como en oraciones
Si hace clic derecho en el encabezado de la columna con texto y selecciona el comando (Transformar), luego puede ver tres comandos responsables de cambiar el registro:
Conveniente y genial, pero en esta lista, por ejemplo, personalmente siempre me ha faltado una opción más: caso como en las oraciones, cuando las mayúsculas (mayúsculas) no se convierten en la primera letra de cada palabra, sino solo en la primera letra de la celda, y el resto del texto cuando Esto se muestra en letras minúsculas (pequeñas).
Esta característica que falta es fácil de implementar con inteligencia artificial Columnas de ejemplos – simplemente ingrese un par de opciones para que Power Query continúe con el mismo espíritu:
Como fórmula aquí, Power Query usa un montón de funciones Texto.Superior и Texto.Inferior, convertir texto a mayúsculas y minúsculas, respectivamente, y funciones Texto.Inicio и Texto.Mid – análogos de las funciones de Excel LEFT y PSTR, capaces de extraer una subcadena del texto desde la izquierda y desde el medio.
Ejemplo 3. Permutación de palabras
A veces, al procesar los datos recibidos, es necesario reorganizar las palabras de las celdas en una secuencia determinada. Por supuesto, puede dividir la columna en columnas de palabras separadas por el separador y luego volver a pegarlas en el orden especificado (no olvide agregar espacios), pero con la ayuda de la herramienta Columna de ejemplos todo será mucho más fácil:
Ejemplo 4: Solo números
Otra tarea muy importante es sacar solo números (números) del contenido de la celda. Como antes, después de cargar datos en Power Query, vaya a la pestaña Agregando una columna – Columna de ejemplos y complete un par de celdas manualmente para que el programa entienda qué es exactamente lo que queremos obtener:
¡Bingo!
Nuevamente, vale la pena mirar la parte superior de la ventana para asegurarse de que Query haya generado la fórmula correctamente; en este caso, contiene una función. Texto. Seleccione, que, como puede suponer, extrae los caracteres dados del texto de origen según la lista. Posteriormente, esta lista, por supuesto, se puede editar fácilmente en la barra de fórmulas si es necesario.
Ejemplo 5: solo texto
De manera similar al ejemplo anterior, puede extraer y viceversa: solo el texto, eliminando todos los números, signos de puntuación, etc.
En este caso, se utiliza una función que ya tiene un significado opuesto: Text.Remove, que elimina caracteres de la cadena original de acuerdo con una lista dada.
Ejemplo 6: extracción de datos de una papilla alfanumérica
Power Query también puede ayudar en casos más difíciles, cuando necesita extraer información útil de la papilla alfanumérica en una celda, por ejemplo, obtener el número de cuenta de la descripción del propósito del pago en un extracto bancario:
Tenga en cuenta que la fórmula de conversión generada por Power Query puede ser bastante compleja:
Para facilitar la lectura y la comprensión, se puede convertir a una forma mucho más sensata utilizando un servicio en línea gratuito. Formateador de consultas de energía:
Algo muy útil: ¡respeto a los creadores!
Ejemplo 7: Conversión de fechas
Columna de ejemplos también se puede aplicar a las columnas de fecha o fecha y hora. Cuando ingresa los primeros dígitos de una fecha, Power Query le mostrará una lista de todas las opciones de conversión posibles:
De modo que puede convertir fácilmente la fecha original a cualquier formato exótico, como "año-mes-día":
Ejemplo 8: Categorización
Si usamos la herramienta Columna de ejemplos a una columna con datos numéricos, funciona de manera diferente. Supongamos que tenemos los resultados de las pruebas de los empleados cargados en Power Query (puntajes condicionales en el rango de 0 a 100) y usamos la siguiente gradación condicional:
- Masters – aquellos que obtuvieron más de 90
- Expertos – puntuados de 70 a 90
- Usuarios – de 30 a 70
- Principiantes: aquellos que obtuvieron menos de 30
Si agregamos una columna de los ejemplos a la lista y comenzamos a organizar estas gradaciones manualmente, muy pronto Power Query tomará nuestra idea y agregará una columna con una fórmula, donde los operadores se anidan entre sí. if Se implementará una lógica muy similar a la que necesitamos:
Nuevamente, no puede presionar la situación hasta el final, pero haga clic en OK y luego corrija los valores de umbral que ya están en la fórmula; es más rápido de esta manera:
Conclusiones
sin duda una herramienta Columna de ejemplos no es una “píldora mágica” y, tarde o temprano, habrá situaciones no estándar o casos especialmente descuidados de una “granja colectiva” en los datos, cuando Power Query fallará y no podrá resolver lo que queremos correctamente para nosotros. Sin embargo, como herramienta auxiliar, es muy bueno. Además, al estudiar las fórmulas que generó, puede ampliar su conocimiento de las funciones del lenguaje M, que siempre será útil en el futuro.
- Análisis de texto con expresiones regulares (RegExp) en Power Query
- Búsqueda de texto difuso en Power Query
- Relleno Flash en Microsoft Excel