Guía Avanzada de Excel — Volumen 5
Power Query: Transformación y Automatización de Datos
Power Query es el motor de transformación de datos integrado en Excel, pensado para
automatizar el proceso de limpieza y combinación de información antes de que llegue a
una tabla o a un modelo de análisis. Dominarlo supone dejar de repetir manualmente los
mismos pasos de limpieza cada vez que se recibe un archivo nuevo.
1. El concepto de consulta y el editor de Power Query
Una consulta de Power Query registra una secuencia de pasos de transformación
aplicados a un origen de datos, ya sea un archivo de Excel, un archivo de texto, una base
de datos o una carpeta con múltiples archivos. Lo relevante no es el resultado final, sino
la secuencia de pasos, porque esa secuencia se vuelve a ejecutar automáticamente cada
vez que se actualiza la consulta, aplicando exactamente la misma limpieza a los datos
nuevos.
1.1 El panel de pasos aplicados
Cada acción realizada en el editor, como eliminar una columna, cambiar un tipo de dato o
dividir un texto, se registra como un paso independiente y visible en el panel lateral. Esto
permite revisar, reordenar o eliminar pasos concretos sin tener que repetir todo el proceso
desde cero, y resulta especialmente útil para depurar errores cuando la fuente de datos
cambia ligeramente su estructura.
2. Transformaciones esenciales
• Combinar consultas (Merge): equivalente a un JOIN de bases de datos, permite cruzar
dos tablas por una o varias columnas comunes, sustituyendo a decenas de fórmulas
BUSCARV.
• Anexar consultas (Append): apila verticalmente varias tablas con estructura similar, ideal
para consolidar archivos mensuales en una única tabla histórica.
• Dividir columnas: separa el contenido de una columna en varias, por delimitador, por
número de caracteres o por posición, de forma mucho más flexible que el asistente
clásico de texto en columnas.
• Columna condicional: crea una nueva columna aplicando reglas lógicas equivalentes a
un SI anidado, pero con una interfaz visual mucho más manejable para reglas con
muchas condiciones.
• Agrupar por (Group By): resume los datos por una o varias columnas, calculando sumas,
promedios o recuentos, de forma similar a una tabla dinámica pero integrada en el flujo
de transformación.
3. El lenguaje M: más allá de la interfaz visual
Todas las transformaciones realizadas mediante la interfaz gráfica se traducen en código
escrito en el lenguaje M, visible y editable en el editor avanzado. Aunque la mayoría de
los usuarios no necesitan escribir M desde cero, entender su estructura permite resolver
casos que la interfaz visual no cubre directamente, como transformaciones condicionales
complejas o la creación de funciones personalizadas reutilizables en varias consultas.
3.1 Funciones personalizadas
M permite definir funciones propias que encapsulan una secuencia de transformaciones y
se aplican después a distintos orígenes de datos. Esto es especialmente útil cuando se
procesa periódicamente una carpeta con archivos de estructura idéntica: se define una
función que limpia un archivo individual, y luego se aplica automáticamente a todos los
archivos de la carpeta mediante la transformación de columna de tipo función.
4. Procesamiento de carpetas y consolidación automática
Una de las aplicaciones más potentes de Power Query es la consolidación automática de
múltiples archivos con estructura similar, como los informes mensuales que envía cada
delegación de una empresa. Al conectar con una carpeta en lugar de con un archivo
individual, Power Query genera automáticamente una tabla con la lista de archivos, aplica
la función de limpieza definida previamente a cada uno de ellos, y consolida el resultado
en una sola tabla. Cuando se añade un archivo nuevo a la carpeta, basta con actualizar la
consulta para que se incorpore automáticamente al análisis, sin ninguna intervención
manual adicional.
5. Rendimiento y buenas prácticas
• Filtrar y eliminar columnas innecesarias lo antes posible en la secuencia de pasos, para
que las transformaciones posteriores trabajen con menos volumen de datos.
• Evitar cambios de tipo de dato repetidos e innecesarios; cada cambio de tipo genera un
paso adicional que consume tiempo de procesamiento.
• Deshabilitar la carga de consultas intermedias a la hoja de cálculo cuando solo se
utilizan como paso previo de otra consulta, cargándolas únicamente al modelo de datos.
• Nombrar las consultas y los pasos con nombres descriptivos, especialmente en
proyectos que se van a mantener a largo plazo o a compartir con otros analistas.
• Aprovechar el plegado de consultas (query folding) cuando el origen es una base de
datos, de forma que las transformaciones se ejecuten en el servidor de origen y no en el
propio equipo.
6. Power Query como puerta de entrada al análisis moderno
Power Query, combinado con Power Pivot y las tablas dinámicas, completa el ciclo de
análisis de datos dentro de Excel: extracción y limpieza automatizada, modelado
relacional y presentación final. Adoptar esta forma de trabajar reduce drásticamente el
tiempo dedicado a preparar la información manualmente cada vez que llegan datos
nuevos, y deja más tiempo disponible para el análisis en sí mismo, que es, en última
instancia, el objetivo real de cualquier hoja de cálculo.
7. Conexión a bases de datos y servicios web
Además de archivos locales, Power Query se conecta de forma nativa a bases de datos
relacionales como SQL Server, MySQL o PostgreSQL, así como a servicios web que
exponen datos en formato JSON o XML. Esta capacidad convierte a Excel en un cliente
ligero de análisis capaz de consumir directamente los mismos datos que utilizan las
aplicaciones corporativas, sin necesidad de exportaciones manuales intermedias.
7.1 Parámetros de conexión
Definir parámetros dentro de Power Query, como el nombre del servidor, la base de datos
o un rango de fechas, permite reutilizar la misma consulta en distintos entornos, por
ejemplo, para alternar entre un entorno de pruebas y uno de producción sin reescribir la
lógica de conexión. Los parámetros también facilitan que otros usuarios adapten una
consulta compartida a su propio contexto sin tener que editar el código M subyacente.
7.2 Plegado de consultas en bases de datos
Cuando el origen es una base de datos, Power Query intenta traducir las
transformaciones aplicadas en la interfaz a instrucciones SQL que se ejecutan
directamente en el servidor, en lugar de traer todos los datos en bruto al equipo local y
filtrarlos después. Este comportamiento, conocido como plegado de consultas, mejora
enormemente el rendimiento, pero se rompe en cuanto se introduce un paso que la base
de datos no puede traducir, por lo que conviene ordenar las transformaciones colocando
primero las que sí son plegables.
8. Plantillas reutilizables y estandarización
En organizaciones donde varios analistas repiten procesos de limpieza similares sobre
archivos de distintos orígenes, conviene construir plantillas de consulta reutilizables en
lugar de reconstruir la lógica cada vez desde cero. Guardar una consulta como plantilla,
documentando claramente qué formato de archivo de entrada espera y qué columnas de
salida produce, reduce errores de interpretación entre distintos miembros del equipo y
acelera la incorporación de nuevos analistas al flujo de trabajo existente.
Una práctica recomendable consiste en mantener un libro de referencia con las funciones
personalizadas de M más utilizadas por el equipo, de modo que cualquier consulta nueva
pueda importar y reutilizar esa lógica común en lugar de duplicarla, de forma similar a
como una biblioteca de funciones se comparte entre distintos proyectos de programación.
9. Gobernanza y documentación de los flujos de transformación
A medida que el número de consultas de Power Query crece dentro de una organización,
la falta de documentación se convierte en el principal obstáculo para mantener el sistema
con el tiempo. Cuando la persona que construyó una consulta compleja deja el puesto o
cambia de proyecto, cualquier modificación posterior sin documentación adecuada corre
el riesgo de introducir errores silenciosos.
• Documentar en cada consulta, mediante la descripción de pasos o un comentario en el
editor avanzado, el propósito general de la transformación y cualquier decisión no
evidente, como por qué se excluyen ciertos registros.
• Mantener un inventario centralizado de las consultas activas en la organización,
indicando su origen de datos, su frecuencia de actualización y quién es responsable de
su mantenimiento.
• Establecer una convención de nombres consistente para consultas, pasos y parámetros,
de forma que cualquier persona del equipo pueda entender la estructura sin depender de
quien la creó originalmente.
• Revisar periódicamente las consultas en producción para detectar pasos obsoletos o
fuentes de datos que ya no están disponibles, evitando que los errores se descubran
solo cuando el informe falla.
Esta disciplina de gobernanza convierte a Power Query de una herramienta de
productividad individual en una infraestructura de datos fiable y sostenible dentro de la
organización, capaz de soportar el crecimiento del volumen de información sin perder el
control sobre su calidad.
10. Errores frecuentes al empezar con Power Query
• Aplicar el cambio de tipo de datos al final del proceso en lugar de al principio, lo que
provoca errores de conversión difíciles de rastrear cuando ya se han aplicado varias
transformaciones adicionales.
• Cargar directamente en la hoja de cálculo consultas que en realidad solo se necesitan
como paso intermedio para otra consulta, inflando innecesariamente el tamaño del
archivo final.
• Codificar manualmente rutas de archivo o nombres de servidor dentro de los pasos de
una consulta, en lugar de utilizar parámetros, lo que obliga a editar el código M cada vez
que cambia el entorno.
• Ignorar los mensajes de advertencia sobre el nivel de privacidad de los orígenes de
datos, que pueden bloquear silenciosamente el plegado de consultas o la combinación
de fuentes distintas.
• No revisar el paso 'Tipo cambiado' que Power Query añade automáticamente al conectar
con un origen, que en ocasiones interpreta incorrectamente el tipo de una columna y
provoca errores más adelante en la consulta.
Evitar estos errores desde el principio ahorra muchas horas de depuración posterior y
sienta las bases para que las consultas construidas hoy sigan siendo fiables cuando el
volumen de datos o el número de fuentes conectadas crezca con el tiempo.