Ediciones ENI Editorial | líder en informática
Introducción a Power Query
Introducción
Power Query es una herramienta de Microsoft integrada en Excel (desde la versión 2010) que permite limpiar, transformar y cargar datos (proceso ETL - Extract, Transform, Load).
Se trata de una herramienta fundamental para los analistas, ya que los datos mal estructurados o sin limpiar pueden llevar a análisis incorrectos.
Power Query automatiza las tareas de preparación de datos, lo que optimiza el proceso y minimiza la posibilidad de errores. Una vez los datos están limpios y transformados, se pueden usar fácilmente para crear análisis detallados e informes mediante tablas dinámicas.
Esta herramienta ha transformado la manipulación de datos, como lo hicieron las tablas dinámicas en la década de 1990.
¿Qué es un ETL?
Un ETL permite realizar tres acciones clave:
-
Extract: conectarse a una o varias fuentes para recuperar los datos deseados;
-
Transform: limpiar, dar formato y agregar los datos para hacerlos utilizables;
-
Load: importar los datos transformados a un lugar específico para su análisis.
Con Power Query, estas operaciones se realizan de manera intuitiva y automatizada, haciendo que la manipulación de datos sea más accesible, incluso para usuarios que no son expertos en programación.
Importar y transformar datos con Power Query
El objetivo de esta sección es abordar la importación y las transformaciones básicas con Power Query. Para ello, se utilizará el archivo Query.xlsx.
Los datos tienen el siguiente aspecto:
El objetivo de este caso práctico es:
-
importar los datos en Power Query;
-
eliminar la columna de vendedores;
-
copiar hacia abajo el valor 2025 que está presente únicamente en la primera fila de la columna Año;
-
filtrar los datos para enfocar el análisis en las categorías Muebles y Electrónica;
-
después del procesamiento, cargar los datos en Excel directamente en forma de tabla dinámica.
Importar datos
Power Query permite conectarse a una gran variedad de fuentes, tales como:
-
archivos de Excel;
-
bases de datos SQL, Access;
-
sitios web;
-
servicios en línea (API, SharePoint).
Importar datos de un archivo Excel
Abrir un nuevo libro de Excel.
En la pestaña Datos, dentro del grupo Obtener y transformar datos, seleccione Obtener datos - De un archivo y luego Desde un libro de Excel.
Se abre el cuadro de diálogo Importar datos para seleccionar el archivo.
Seleccione el archivo Query.xlsx y haga clic en Importar. Aparecerá el cuadro de diálogo Navegador.
Seleccione la hoja Hoja1, y se mostrará una vista previa de los datos.
Esta vista previa permite comprobar los datos antes de cargarlos o transformarlos.
Haga clic en Transformar datos.
El editor de Power Query aparece en pantalla.
En el lado derecho, en el panel Configuración de la consulta - PROPIEDADES, nombre la consulta como Ventas_enero y pulse Intro.
Limpiar y transformar los datos
Después de importar los datos, puede comenzar la fase de transformación.
Power Query ofrece una amplia gama de transformaciones automáticas.
Eliminar columnas
A veces, no todas las columnas de un archivo son necesarias. Power Query permite eliminar aquellas que no son relevantes.
En nuestro caso, vamos a eliminar la columna Vendedor.
Seleccione la columna Vendedor, haga clic derecho y luego haga clic en Quitar.
Rellenar hacia abajo
En la columna Año, solo la primera fila muestra 2025. Las demás filas actualmente tienen el valor null pero también corresponden al año 2025.
Para copiar el valor 2025 hacia abajo, seleccione la columna Año.
En la cinta de opciones, haga clic en la pestaña Transformar, dentro del grupo Cualquier columna, seleccione Rellenar - Abajo.
El valor 2025 se ha copiado en toda la columna:
Filtrar los datos
Al igual que en Excel, es posible filtrar los datos para trabajar únicamente con los elementos relevantes.
Para nuestro análisis, queremos solo las categorías Muebles y Electrónica.
En la columna Categoría, abra el menú desplegable del filtro ubicado en el lado derecho del encabezado de la columna.
Desmarque el campo Decoración.
Haga clic en Aceptar.
La columna se ha filtrado.
Cada uno de estos pasos de transformación se guarda automáticamente en el editor de Power Query. Aparecen en el panel de Configuración de la consulta, dentro de la sección PASOS APLICADOS.
También se encuentran en forma de código M, un lenguaje de programación dedicado a la manipulación de datos...
Actualizar los datos
Una vez que los datos se hayan importado y transformado, si el archivo fuente o la base de datos se actualiza (por ejemplo, se añaden nuevas filas o se modifican valores existentes), puede actualizar fácilmente los datos en Power Query para reflejar estos cambios. Para hacerlo, basta con hacer clic en el botón Actualizar todo en la pestaña Datos de la cinta de opciones de Excel. Power Query se reconecta con la fuente de datos, importa los nuevos datos y vuelve a aplicar todos los pasos de transformación definidos previamente.
Cambie algunos valores en el archivo Query.xlsx.
En la pestaña Datos, en el grupo Consultas y conexiones, haga clic en el botón Actualizar todo.
Los datos están actualizados.
Automatizar la preparación de datos
Uno de los puntos fuertes de Power Query reside en su capacidad para automatizar la preparación de datos. Una vez definidos los pasos de limpieza y transformación, pueden reutilizarse y actualizarse en datos nuevos y similares, sin tener que rehacerlos manualmente.
Supongamos que todos los meses recibe un archivo de ventas mensuales en un formato idéntico.
Una vez que haya configurado la consulta de Power Query para limpiar y estructurar los datos del primer mes, podrá actualizar la consulta cada mes con el nuevo archivo de ventas, sin necesidad de repetir manualmente los pasos de transformación.
Hemos creado la consulta Ventas_enero en la sección anterior. Ahora, en febrero, hemos recibido los siguientes datos:
Encontrará estos datos en el archivo Ventas_Febrero.xlsx.
Los datos son diferentes, pero la estructura es la misma. Podremos utilizar los pasos creados anteriormente en Power Query.
Primer paso: copiar la consulta
Vuelva al archivo Informe de ventas.xlsx si es necesario, vaya a la pestaña Datos - grupo Consultas y conexiones y haga clic en el botón Consultas y conexiones.
Aparece el panel Consultas y conexiones.
Haga doble clic en la consulta Ventas_enero.
Se abre el editor Power Query.
En la parte izquierda de la pantalla, haga clic con el botón derecho en la consulta Ventas_enero y seleccione Duplicar.
La consulta se ha duplicado....
Otras funciones de Power Query
Power Query es una auténtica aplicación de reprocesamiento y manipulación de datos. Como tal, incluye muchas otras funciones.
Añadir consultas
Cuando se utiliza la opción Añadir consultas, se combinan datos de distintas fuentes en una única consulta. Esto resulta especialmente útil cuando se trabaja con conjuntos de datos similares procedentes de archivos o bases de datos distintos.
En la cinta del editor Power Query, en la pestaña Inicio - Combinar, haga clic en el botón Anexar consultas - Anexar consultas para crear una nueva.
Aparece en pantalla el cuadro de diálogo Anexar.
En la lista desplegable Primera tabla, seleccione Ventas_enero, y en la lista desplegable Segunda tabla, seleccione Ventas_Febrero.
Haga clic en Aceptar.
Aparece una nueva tabla denominada Anexar1.
Contiene datos de enero y febrero.
En el panel Configuración de la consulta, asigne a la consulta el nombre Informe_consolidado y pulse Intro.
Ahora puede crear un informe consolidado en forma de tabla dinámica utilizando estos datos.
En la pestaña Inicio - grupo Cerrar, haga clic en Cerrar y cargar - Cerrar y cargar en.
Aparece el cuadro de diálogo Importar datos.
Seleccione la opción Informe de tabla dinámica.
Haga clic en Aceptar
Aparece un informe de tabla dinámica en blanco.
Coloque los campos...
Pivotar/despivotar los datos
Las funciones pivotar y despivotar en Power Query permiten transformar las filas, columnas y viceversa, lo que ayuda a estructurar los datos para un uso óptimo de las tablas dinámicas.
La función Pivotar
La función Pivotar en Power Query permite transformar las filas en columnas. Por ejemplo, si dispone de un archivo de ventas mensuales con los meses en filas, puede usar la función pivotar para transformar esas filas en columnas para cada mes, los que le permite comparar fácilmente las ventas mensuales.
Supóngase un archivo de ventas con las siguientes columnas:
Encontrará estos datos en la hoja Pivotar del archivo Pivotar_Despivotar.xlsx.
Haga clic con el botón derecho en la tabla y a continuación elija Obtener datos de Tabla/Rango.
El editor Power Query se abre con los datos de la tabla.
En la pestaña Transformar de Power Query, en el grupo Cualquier columna seleccione Columna dinámica.
Aparece el cuadro de diálogo Columna dinámica.
En la lista desplegable seleccione Importe.
Haga clic en Aceptar.
La columna Ciudad se ha movido cada una de las ciudades se ha convertido en columna, el importe de las ventas se ha agrupado por ciudad:
La función Despivotar
La función Despivotar es lo contrario de la función Pivotar. Transforma las columnas en filas.
Para crear una tabla dinámica (TD)...
Conclusión
Power Query se ha consolidado como una herramienta fundamental de Excel para preparar y transformar datos de forma rápida, lo que mejora la calidad de los análisis y optimiza el tiempo de trabajo. En el próximo capítulo se abordará Power Pivot, una herramienta que permite crear modelos de datos avanzados y realizar análisis complejos con tablas dinámicas.