12 TRUCOS POWERQUERY DESVELADOS

12 TRUCOS POWERQUERY DESVELADOS

Imperdible sesión junto a Rafael González, John Vergara, Ramón Barrull y Sara Lozano

Cuatro MVP, una sesión y trucos de Power Query que no salen en el asistente

Esta sesión reunió a cuatro Microsoft MVP —Nacho Cardenal, John Vergara, Ramón Morera y Rafa Barreda— para enseñar, por turnos, trucos de Power Query que casi nadie usa. El hilo conductor de todos ellos es el mismo: el asistente de Power Query deja el código "clavado" con valores fijos, y basta con abrir el editor avanzado y tocar una línea para que la consulta deje de romperse.

Aquí tienes los trucos de la primera ronda, explicados para que puedas aplicarlos hoy mismo.

Consolidar una carpeta cuando cada Excel tiene la hoja con otro nombre

Combinar todos los ficheros de una carpeta es de las cosas que Power Query hace a golpe de clic. El problema aparece cuando el fichero de enero tiene la hoja llamada Enero, el de febrero Febrero, y así sucesivamente. El asistente fija el nombre de la hoja en el código, así que en cuanto llega un archivo cuya hoja se llama distinto, la consulta da error.

La solución de Nacho es ir a la consulta Transformar archivo, abrir el editor avanzado y sustituir el filtro por nombre de hoja por un índice posicional: en lugar de decir "búscame la hoja llamada Datos", se le dice "coge la primera fila del origen y su campo Data". Recuerda que Power Query empieza a contar en cero, así que la primera fila es la fila 0.

Con ese cambio, la consolidación funciona sin importar cómo se llame la hoja de cada archivo. Es especialmente útil con ficheros que salen automáticos de un ERP, que suelen tener una estructura simple y predecible.

Mini truco dentro del truco: al abrir el archivo de ejemplo aparecen varios parámetros. El segundo controla si la primera fila se toma como cabecera. Si lo pones a verdadero, te ahorras el paso manual de promover encabezados en cada archivo.

Documentar y hacer copia de seguridad de tus consultas

John enseñó algo que sorprende: selecciona una consulta en el panel Consultas y conexiones, pulsa Ctrl+C y pégala en el Bloc de notas. Lo que aparece es la consulta entera —su código M y sus metadatos— en formato de texto.

Esto tiene tres usos inmediatos:

  • Backup: guardas el código de todas tus consultas en un fichero de texto, fuera del Excel.
  • Documentación: puedes seleccionar varias consultas a la vez y pegarlas todas de golpe, cada una identificada.
  • Migración: si copias una consulta y la pegas en otro Excel, o incluso en Power BI, se lleva consigo todas las consultas de las que depende.

Ese último punto es el más potente. Si tu consulta Datos depende de Tipos, y Tipos depende de Columnas, al copiar solo Datos se pegan las tres. Puedes comprobar esas relaciones en el editor de Power Query, en Vista, Dependencias de consulta.

Añadir el año del nombre del archivo sin funciones personalizadas

Ramón planteó un caso con varias trampas a la vez: carpeta de CSV, el año solo aparece en el nombre del fichero, hay dos filas basura arriba, la cabecera está desplazada y los datos vienen dinamizados.

Lo resolvió entero dentro de una única columna personalizada, sin crear ninguna función auxiliar. La secuencia es:

  1. Folder.Files sobre la ruta de la carpeta.
  2. Text.Replace sobre la columna Name para quitar la extensión, y Number.From para convertir el año en número.
  3. Csv.Document sobre la columna Content, indicando el delimitador (punto y coma en su caso).
  4. Table.PromoteHeaders envolviendo a Table.Skip para saltar las dos primeras filas y promover la cabecera de una sola vez.
  5. Table.UnpivotOtherColumns para desdinamizar.
  6. Table.AddColumn para incorporar el año ya calculado, aprovechando el último argumento para tiparlo como número entero.
  7. Y el cierre: recuperar esa columna como lista y aplicarle Table.Combine.

La gracia de terminar con Table.Combine en vez de expandir columnas es que respeta los nombres de las columnas sin que tengas que enumerarlos, así que no queda ni un nombre de columna escrito a fuego en el código.

El operador de coalescencia para los nulos

Rafa cerró la ronda con el truco más corto y más reutilizable. En Power Query, null es ausencia de valor, y no es lo mismo que cero, que vacío ni que un espacio. Si sumas dos columnas y una tiene null, el resultado no es la otra cifra: es null.

Lo habitual es meter un paso previo de "reemplazar valores" para cambiar los nulos por ceros. Pero existe el operador de coalescencia, que se escribe con dos signos de interrogación de cierre seguidos, y que significa "si esto es nulo, usa esto otro":

[Venta 1] ?? 0 + [Venta 2] ?? 0

Con eso te ahorras el paso de reemplazo entero. Es azúcar sintáctico para una condicional, y funciona solo con nulos, no con errores (para los errores existe try ... otherwise). En DAX tienes el equivalente como función.

Extra: agrupar por rachas consecutivas

En la segunda ronda, Nacho enseñó los dos parámetros ocultos de Table.Group, que el asistente nunca te escribe:

  • El comparador Comparer.OrdinalIgnoreCase, que hace que "Alejandro" y "alejandro" cuenten como la misma persona.
  • El tipo de agrupación GroupKind.Local, que en lugar de agrupar todo el conjunto agrupa tramos consecutivos: cada vez que el valor cambia, empieza un grupo nuevo.

Ese segundo parámetro es la respuesta a preguntas del tipo "¿alguien ha hecho más de 3 horas seguidas?", que con la agrupación normal son muy difíciles de contestar.

Conclusión

El patrón que comparten los cuatro trucos: el asistente de Power Query es excelente para empezar, pero deja el código lleno de valores fijos —nombres de hoja, nombres de columna, listas de campos— y ahí es donde las consultas se rompen al mes siguiente. Aprender a abrir el editor avanzado y cambiar una línea no es una cuestión de elegancia: es lo que hace que el mantenimiento deje de doler.

Más contenido de Excel en InflueXcel