Luego de varios años utilizando Power Bi Desktop junto a muchos usuarios veo que una mala práctica no deja de implementarse. En un principio pense que se ajustaba a que la herramienta era nueva o los usuarios inexpertos. Sin embargo estoy pensando que la razón es por la falta de contenido que hay sobre Power Query.
De lo que estoy hablando es de la mala costumbre de generar una enorme cantidad de pasos en el editor de consultas para limpiar orígenes de datos. Hace un tiempo encontré corto video de un indio generando tips para la reducción de pasos en Power Query lo cual me llevó a pensar "hay que generar contenido de esto".
Este artículo mostrará algunos ejemplos diarios de Power Query para reducir los pasos de nuestras consultas
¿Por qué reducir pasos de nuestra consulta?
Lo primero que vamos a hablar antes de ver los tips es entender el porque es necesaria esta acción. La respuesta esta enfocada en dos beneficios de los cuales su importancia es igualitaria.
- Performance: si bien puede resultar absurdo que mejore la velocidad porque al fin y al cabo se genera un único script que al final del día ejecutaría las mismas funciones, el hecho de no tenerlas consolidadas en los mismos pasos puede generar peor performance. Tal como cualquier lenguaje como SQL, depende como se genera la lógica de la consulta en el tiempo de respuesta que tardaría en responder. Podemos ver como medirlo o evidenciarlo con el siguiente video: https://www.youtube.com/watch?v=2bzJFrJPg_c
- Mantenimiento: una cuestión que se deja mucho de lado y es brutalmente importante es el mantenimiento. Generar un mínimo de pasos concretos ayuda exponencialmente la facilidad de lectura de un ETL. Imaginen que tener que leer 15 "Cambiar Tipo de datos" para encontrar el error en una columna que no existe más sería más complejo que si solamente tuvieramos un solo paso "Cambiar Tipo de datos". No solo para ejecutar acciones sino también para entender lo que esta realizando el motor en un vistazo.
Ahora que sabemos la importancia de reducir pasos del editor de consulta veamos algunos ejemplos. Cabe admitir que el 80% de los casos de exceso de pasos ocurren por dos motivos. Por un lado el "renombrar" nuestras columnas y por otro el "cambiar tipo de datos" de nuestras columnas. Esos dos suelen ser los que más basura introducen en el editor. Una forma de evitarlo es pensar la lógica de la consulta primero y luego ejecutar pasos en lugar de ir haciendo clicks por cada cosa que se nos ocurre.
IMPORTANTE: la clave para llegar a un hábito de reducción de columnas es tener SIEMPRE visible la barra de fórmulas en el editor de consultas.
Cambiar tipo de datos
Este paso solo deberíamos aplicar si nuestro motor no tiene diferenciado el tipo y de ser posible una sola vez. Hay motores, como los de las base de datos, que nos importarán los datos ya diferenciados evitando el paso. Pero hay otros que no lo harán y es importante que cada columna este con su tipo de datos. Comencemos evitando la autogeneración de este paso de parte de PowerBi para que no nos genere el paso automaticamente porque sino lo ejecuta al principio y luego de acciones como "Dividir Columna". Esto podemos desactivarlo en la configuración de desktop como muestra la siguiente imagen:
Una de las clasicas necesidades de convertir tipos de datos es luego de generar una columna personalizada (add custom column). Tras agregar una columna personalizada el motor suele asignar el tipo de datos "Cualquiera ABC123″. Lo importante es prestar atención a lo que nuestra función hace para encontrar estos tips. Veamos el ejemplo a partir de una columna fecha queremos una columnas con el mes anterior:
Fijensé que al interpretar el código la descripción de nuestra función indica que hay un parámetro que no usamos. El último parametro especifica "columnType as nullable". Cuando creamos una columna personalizada podemos ajustar el tipo de datos. Podríamos especificar que es tipo date de la siguiente manera
Renombrar columnas
La necesidad de mantener una buena capa semántica entendible para nuestros usuarios suele llevarnos a renombrar mucho nuestras columnas. Al igual que el cambio de tipos voy a decir que este paso debería ejecutarse idealmente una vez o ninguna.
Siguiente la lógica anterior vamos a prestar atención al código de las funciones para encontrar que las funciones suelen demandar el nombre de la columna tras una transformación lo cual evitaría que generemos nuevos pasos de "Renombrar Columnas". Veamos algunos ejemplos:
Al agregar una columna personalizada puedo cambiar el nombre, pero si me olvide puedo ver en el código en que parte cambiarlo
Semejante al anterior de una nueva columna personalizada, la mayoría de las acciones bajo la pestaña "Agregar Columna" suele tener una forma de cambiar su nombre. Un ejemplo de pasos autogenerados como una "Resta" de dos columnas:
Como podemos apreciar allí tranquilamente podríamos reemplazar en el código el texto "Substraction" que se generó automaticamente por el nombre deseado en lugar de generar un nuevo paso.
Un clásico ejemplo para renombrar es el dividir columnas (split) por delimitador. Por ejemplo si quisiera dividir mi columna Name que tiene el formato "Apellido,Nombre" en dos columnas, lo más probable es que el paso se vea así
En el código volvemos a apreciar que al final nos indica el nombre que tendrán las columnas luego de dividir la original "Name". Ahi podría reemplazar Name.1 por Apellido y Name.2 por Nombre.
Como estos hay muchos más, cierro el renombre con otro clásico que sería "Expandir registros". Cuando vinculamos tablas, leemos APIs, etc. Suele ocurrir que tenemos que expandir registros. El paso de expansión tiene dos detalles importantes. Por un lado elegir si queremos mantener el nombre de la tabla/registro como prefijo y como siempre al final del código de Power Query veremos los nombres que se les daría a una columna si queremos cambiarlos.
Ya de ante mano nos pregunta por el prefijo. Si no nos dimos cuenta también podríamos cambiarlo como en el split:
Reordenar columnas
Existen pasos completamente innecesarios que no aportan valor a nuestra consulta final pero si le quitan performance. Este es el caso de reordenar columnas. Esta acción es meramente estética y únicamente válida para el trabajo en el editor de consultas puesto que una vez aplicado y cerrado el editor Power Bi las pondrá en orden alfabético en nuestra vista de campos para arrastrar. A menos que tengamos operaciones muy complicadas de entender si no tenemos los campos cercanos unos a otros, esta operación termina siendo un costo de performance para la consulta.
Usar primera fila de encabezado
Leer la constitución de las funciones que usamos puede ayudar de muchas formas como pudimos apreciar en el cambio de tipo de datos. Otra opción que nos puede ayudar es a usar la primera fila de datos como encabezado (Promote Headers). Cuando nos conectamos a Excel suele ocurrir que el motor nos añade una fila para reconocer los encabezados. Si la conexión es a un archivo xlsx podemos ahorrar ese pasó puesto que la función de lectura Excel.Workbook tiene un parámetro para leerlos. ¿Cómo lo supimos? leyendo la función. Veamos la siguiente imagen:
Podríamos cambiar ese null por true para que el resultado de clickear la Hoja o Tabla deseada ya incorpore la primera fila como Encabezados sin necesidad de crear otro paso.
Reagrupar mismo tipo de pasos
Antes de comenzar a dar click yendo columnas por columna, es necesario analizar toda la tabla y pensar la lógica que queremos implementar. De esa forma nos concentramos en realizar una acción común para toda la tabla y no ir repitiendo pasos columna por columna. Puede parecer lo mismo para alguien que no esta acostumbrado ir columna por columna renombrando y cambiando el tipo, pero lo mejor sería hacer el mismo tipo de paso para todas las columnas una vez puesto que provocaría que se acumulen en el código en lugar de generarse muchos pasos para así pasar de la imagen de la izquierda a la de la derecha
Si primero hicieramos todo el renombre quedaría asi el código:
Incluso podríamos editar directamente el código si quisieramos. Mismo caso para el tipo de datos el código se vería así
Si no queremos introducirnos en el código, ya efectuamos el renombrado y luego de trabajar un tiempo recordamos que se nos olvidó una renombrar columna. Podemos evitar la generación de OTRO paso si vamos a ese paso de renombre con un click. Si ejecutamos un paso parados en la acción del mismo paso el motor de porwer query preguntará si queremos crear un nuevo paso o generar el código dentro del mismo.
Esto se podría aplicar a distintos tipos de pasos como "Filtrar filas". Podríamos filtrar filas por distintas condiciones de columnas en un mismo paso en lugar de tener varios pasos que filtren filas.
Otras posibilidades
Junto con estos tips estoy seguro que hay muchísimos más. A medida que más conocemos el lenguaje M más tips conoceremos y podríamos llegar a soluciones más complejas. Por ejemplo si quisieramos extraer el nombre del mes de una columna "Periodo" con el formato "2022-05″ bastaría con crear una nueva columna convirtiendo ese periodo en fecha y extrayendo el formato esperado:
Date.ToText(
Date.From([Periodo])
, "MMMM"
)
De esa forma no fue necesario duplicar periodo para convertirlo a fecha para poder extraer el nombre del mes.
NOTA: Recordemos que si tenemos la posibilidad de ejecutar una consulta nativa al origen de datos, podríamos reducir nuestra consulta. Por ejemplo filtrando filas con un WHERE en lugar de generar pasos varios.
Espero que esto los ayude a reducir los pasos de sus consultas para que sean mantenibles a futuro. ¿Y vos? ¿Qué tips tenes para reducir pasos en el editor de consultas?