Limpieza avanzada con M

Más allá de la interfaz: el lenguaje M

Cuando realizas transformaciones en la interfaz de Power Query, como eliminar columnas o filtrar filas, en realidad estás escribiendo código sin darte cuenta. Cada clic genera una línea en un lenguaje de scripting llamado M. Para ver la magia detrás del telón, puedes ir a la pestaña "Vista" y hacer clic en "Editor avanzado".

El lenguaje M es secuencial. Cada paso de la transformación se basa en el resultado del paso anterior. Imagina que es una receta de cocina: primero picas los ingredientes, luego los mezclas y finalmente los cocinas. No puedes hornear algo que aún no has mezclado.

let
    // Paso 1: Conectar al origen de datos
    Origen = Csv.Document(File.Contents("C:\reportes\ventas.csv"),[Delimiter=",", Encoding=1252]),
    
    // Paso 2: Promover la primera fila como encabezados
    #"Encabezados promovidos" = Table.PromoteHeaders(Origen, [PromoteAllScalars=true]),
    
    // Paso 3: Cambiar el tipo de dato de la columna 'Fecha'
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"Fecha", type date}})
in
    #"Tipo cambiado"

Cada línea (excepto la última) termina con una coma. El resultado final de la consulta se especifica después de la palabra clave in. La verdadera potencia de M se desata cuando necesitas hacer algo que la interfaz no ofrece, como una lógica condicional compleja para crear una nueva columna.

Optimización con Query Folding

Imagina que necesitas analizar un terabyte de datos de ventas de una base de datos SQL. Si Power BI intenta descargar toda esa información a tu computadora para luego filtrarla, probablemente tu máquina se bloqueará o tardará horas. Aquí es donde entra en juego el (o plegado de consultas).

El Query Folding es un proceso en el que Power Query traduce tus pasos de transformación (hechos con clics o con código M) a un lenguaje que la fuente de datos entiende, como SQL. En lugar de traer todos los datos y filtrarlos localmente, le pide a la base de datos: "Oye, antes de enviarme los datos, fíltralos por el año 2023 y agrúpalos por producto". La base de datos, que está optimizada para estas tareas, hace el trabajo pesado y solo envía a Power BI el pequeño subconjunto de datos que realmente necesitas. Esto reduce drásticamente el tiempo de carga y el uso de memoria.

Puedes verificar si el Query Folding está activo haciendo clic derecho en el último paso de tu consulta en el panel "Pasos aplicados". Si la opción "Ver consulta nativa" está habilitada, ¡felicidades!, el plegado está funcionando. Si está en gris, algo en tus transformaciones lo rompió, y Power Query está procesando los datos en tu máquina a partir de ese paso.

Funciones y parámetros reutilizables

¿Qué pasa si tienes que aplicar la misma secuencia de 20 pasos de limpieza a 10 archivos diferentes? ¿Copiar y pegar la consulta 10 veces, cambiando solo el nombre del archivo? Eso es ineficiente y propenso a errores. La solución es crear una función personalizada.

Una función en Power Query es como una plantilla de limpieza. Defines los pasos una vez y luego la "llamas" para cada archivo que necesites procesar. Para hacerlo más dinámico, puedes usar parámetros. Un parámetro es una variable que puedes cambiar fácilmente sin editar el código. Por ejemplo, puedes crear un parámetro para la ruta de la carpeta donde están tus archivos. Si mueves la carpeta, solo actualizas el parámetro una vez, y todas las consultas que lo usan funcionarán correctamente.

Piensa en una función como una receta para un pastel. El parámetro sería el tipo de fruta que usas. La receta (los pasos) es la misma, pero puedes hacer un pastel de manzana, de fresa o de plátano simplemente cambiando un ingrediente.

Crear una función es simple. Primero, crea una consulta de ejemplo con todas las transformaciones que necesitas. Luego, en el Editor Avanzado, agrega (parametro as text) => al principio del código, reemplaza la ruta del archivo codificada con tu parametro, ¡y listo! Acabas de convertir una consulta estática en una función reutilizable.

Normalización de datos

A menudo, los datos no vienen en un formato ideal para el análisis. Un problema común es la en columnas que deberían ser más simples, o tablas que están "pivotadas" de una manera que dificulta la creación de visualizaciones.

Por ejemplo, podrías recibir una tabla de ventas donde cada mes es una columna separada. Este formato es fácil de leer para un humano, pero terrible para una herramienta de BI. Para analizar tendencias a lo largo del tiempo, necesitas una columna de 'Fecha' y una columna de 'Ventas', no doce columnas separadas para los meses.

Producto	Ene-23	Feb-23	Mar-23
Laptop	150	175	200
Teclado	300	280	310

La solución es la "anulación de dinamización" (Unpivot). En Power Query, puedes seleccionar las columnas de los meses, hacer clic derecho y elegir "Anular dinamización de columnas". Esto transformará la tabla a un formato normalizado, mucho más útil para el análisis.

Producto	Mes	Ventas
Laptop	Ene-23	150
Laptop	Feb-23	175
Laptop	Mar-23	200
Teclado	Ene-23	300
...	...	...

La operación inversa, dinamizar (Pivot), también es útil. Si tienes datos normalizados y necesitas crear una tabla de resumen donde ciertos valores se conviertan en columnas, Pivot es la herramienta adecuada. Dominar estas dos transformaciones es clave para dar forma a casi cualquier conjunto de datos.

Ahora que hemos cubierto algunas técnicas avanzadas, es hora de poner a prueba tus conocimientos.

Quiz Questions 1/5

¿Qué lenguaje de scripting se genera automáticamente en segundo plano cuando aplicas transformaciones en la interfaz de Power Query?

Quiz Questions 2/5

¿Cuál es el principal beneficio del "Query Folding" (plegado de consultas) en Power Query?

Dominar estas técnicas te permitirá manejar escenarios de datos complejos con confianza, creando modelos de datos limpios, eficientes y escalables.

Dominio Avanzado de Power BI y Análisis de Datos