En este post descubrirás qué es Power Query y cómo con su ayuda puedes generar un procedimiento que transforme tus datos de forma automática. Ya no tendrás que copiar y pegar datos entre archivos, ni utilizar fórmulas complejas o macros, sino que lo haremos de forma intuitiva y haciendo cuatro sencillos clics. Power Query tiene una interfaz con botones que simplifican muchísimo nuestro trabajo.
¿Qué es Power Query?
Power Query es un complemento gratuito creado por Microsoft para Excel 2010 o superior. Es una herramienta que actúa como ETL, lo que significa que permite extraer datos de distintas fuentes de datos, transformar la forma de los datos según sea necesario, y luego cargarlos en algún sitio para su uso posterior, ya sea en una tabla o en un modelo de datos (Power Pivot).
¿Dónde encuentro Power Query en Excel?
Lo primero que debes saber es que Power Query es un complemento que funciona en la versión de Excel 2010 y posteriores. En las versiones 2010 y 2013, encontrarás el complemento en una solapa separada llamada “Power Query”. Si no ves la solapa puedes activarla siguiendo los siguientes pasos:
- Click en la pestaña “Archivo”,
- En el menú, click en “Opciones”.
- Click en “Complementos”.
- Busca “Administrar” en la parte inferior del diálogo y selecciona “Complementos COM” en la lista desplegable.
- Click en “Ir”
- Selecciona “Microsoft Power Query para Excel” y click en “Aceptar”.
En la versión de Excel 2016 en cambio, Power Query está incluido dentro de la solapa “Datos”.
Usar Power Query para hacer un BuscarV
Primero es importante entender el propósito del ejercicio, cuando hablamos de BuscarV (o VLOOKUP) nos referimos al comando en Excel que realiza una búsqueda de datos en vertical de la columna que hayas especificado. Cuando encuentra el valor buscado, te devuelve tantas columnas como quieras de esa fila buscada.
Configura la carga de datos en Power Query
Antes de comenzar, en la pestaña “Datos”, selecciona “Nueva Consulta”, “Opciones de Consulta”. En “Carga de datos” quita el tilde a las opciones de “Cargar en hoja de cálculo” y de “Cargar en modelo de datos”.
Objetivo del ejercicio y link de descarga
Ahora que ya dispones de Power Query, veamos cómo puedes automatizar la limpieza de tus datos. En el ejercicio que usaremos para mostrarte el procedimiento, partiremos de un archivo Excel con tres hojas:
- Una hoja con el detalle de Ventas de junio 2019
- Una hoja con el detalle de los Managers:
- Una hoja con los costes de los productos:
El primer objetivo será generar una única tabla que muestre en cada fila el detalle de cada venta, que incluya el nombre del manager que realizó la venta, y el coste del producto vendido (lo que tradicionalmente haríamos con buscarv).
Pero lo más importante será lograr que esa tabla se actualice de forma automática cada vez que el archivo Excel de origen se actualice. Es decir, cuando haya nuevas filas de ventas, nuevos managers o nuevos productos, Power Query nos genere automáticamente, en este Excel nuevo, esta tabla ya transformada.
Puedes descargar el archivo desde el siguiente link:
1. Crea una conexión de Power Query entre un documento Excel nuevo y tus fuentes de datos
Abrimos un nuevo documento Excel, dónde generaremos el reporte.
Ahora, en vez de copiar y pegar los datos de origen en este documento, crearemos una conexión con los datos de origen. Gracias a esta conexión, cuando los datos de origen cambien, será sólo necesario actualizar la conexión, y se actualizará nuestro documento de reporting.
En Excel 2016 o posteriores, debes ir a Datos, Nueva Consulta, Desde un Archivo, Desde un Libro:
Seleccionamos el archivo Excel con los datos de origen (el que te has descargado), y se abrirá el siguiente cuadro:
Aquí primero seleccionamos la opción “Seleccionar varios elementos” y luego hacemos click sobre las tres hojas. Por último seleccionamos “Transformar datos”, y se abrirá una nueva ventana de Power Query.
Power Query nos mostrará las 3 consultas en la parte izquierda, es decir los datos de las tres hojas a las cuales nos conectamos. En la parte de arriba podrás ir generando transformaciones a tus datos. Y esas transformaciones se guardarán en forma de pasos (ubicado en la derecha como “Pasos Aplicados”), de manera que se repetirán uno a uno cada vez que se actualice la conexión de datos. Puedes ver que Power Query ya ha generado algunos pasos de forma automática.
2. Genera transformaciones a estos datos, almacenados en forma de pasos
Ahora llevaremos a la consulta de Ventas, los datos del nombre del manager y del coste de los productos. Es lo que tradicionalmente haríamos en Excel con BuscarV. Pero lo haremos en forma de pasos con Power Query, para que los mismos pasos se repitan cada vez que los datos de origen cambien.
Seleccionamos la consulta “Ventas”, y clickeamos en la pestaña “Inicio”, la transformación “Combinar Consultas”. Esta opción nos permite hacer el Buscarv de Excel, pero sin fórmulas y dejando la transformación almacenada como un paso a repetirse en las actualizaciones de datos. Además, nos permite llevar múltiples columnas de otra tabla, en un solo paso.
Se abrirá una nueva ventana, dónde marcaremos la columna que permite generar la relación entre estas dos tablas. En esta caso hacemos click sobre DM en ambas tablas y luego seleccionamos “Aceptar”:
Ahora aparecerá dentro de la consulta de “Ventas”, una nueva columna, con el nombre de la tabla con la que hicimos la transformación “Combinar”. Puedes ver que al lado de la columna “Manager”, aparece un botón con algo parecido a dos flechas.
Al seleccionarlas, elegiremos qué columnas de la tabla de Manager, queremos traer a la tabla de Ventas. Es este caso seleccionamos sólo la columna “Name”, que contiene le nombre del manager, y cambiamos el nombre “Name” por “Manager”, haciendo doble click sobre el encabezado.
Ahora generamos los mismos pasos que hemos realizado para llevar el valor del manager a la tabla de ventas, pero combinando la tabla “Ventas” con la de “Producto” para llevar el dato de coste.
Primero seleccionamos la consulta de “Ventas”, seleccionamos “Combinar consultas”, y en este paso, elegimos la columna “producto” en ambas tablas, y expandimos el valor del coste del producto.
Una vez terminados estos pasos, podremos visualizar en la consulta de “Ventas”, el nombre del manager y el coste del producto.
Si miras a la derecha de la pantalla, verás que cada uno de los pasos realizados, se han guardado en forma de pasos.
3. Carga los datos transformados de Power Query a una tabla Excel
Como paso final en Power Query hacemos click en “Cerrar y Cargar”.Volveremos de esta forma nuevamente la pantalla tradicional de Excel, y a la derecha veremos el detalle de las tres consultas generadas.
Para poder visualizar en una tabla las ventas resultantes de las transformaciones aplicadas, con las columnas de nombre de manager y coste incluidas, hacemos click derecho sobre la consulta “Ventas” y seleccionamos “Cargar en”.
Aquí le indicarás a Excel que muestre los datos transformados por Power Query en una tabla.
A partir de esta tabla podrás crear cuadros de mando utilizando tablas y gráficos dinámicos.
Y lo más interesante será que cuando los datos de origen cambien (por ejemplo cuando tengamos nuestro archivo con las ventas de Julio), podremos actualizar esta tabla simplemente seleccionando “Actualizar Todo” en la pestaña de “Datos”.
Verás que Power Query se encargará de conectarse a los nuevos datos, generará las transformaciones que tú has generado en forma de pasos, y te mostrará en esta tabla excel las ventas de junio y julio, con el nombre del manager, y el coste de la venta.
Te invito a probarlo, simplemente agrega una nueva fila en la hoja de Ventas, en el archivo de ventas que te has descargado de este post. Guárdalo y luego actualiza el documento que hemos creado!
Y si complementas el uso de Power Query con Power Pivot serás capaz de automatizar totalmente la generación de tus reportes y cuadros de mando. Si quieres conocer en detalle todo acerca de estas herramientas, te esperamos en los cursos que realizamos tanto en nuestras oficinas como In-Company– ¿A qué esperas? ¡Son hasta 100% bonificables!
