Usos del Power Query - integrar datos de distintos archivos

Una de las tareas más frecuente de todo analista es integrar datos de distintos cuadernos. Excel nos permite hacerlo de varias maneras, desde copiar y pegar (la menos recomendable) hasta usando MsQuery, Access o Vba. El nuevo add-in Power Query nos facilita enormemente la tarea, como mostraremos en esta nota.

A los efectos del ejemplo vamos a suponer el siguiente escenario:

• tres sucursales reportan las ventas en cuadernos de Excel;
• los cuadernos están guardados en una carpeta común a las tres;
• las hojas del reporte tienen la misma estructura (más adelante veremos qué pasa cuando esta condición no se cumple).

Nuestro objetivo es integrar los datos de las tres sucursales en una única tabla de manera que podamos analizarlos con facilidad usando tablas dinámicas.

Empezamos por abrir uno de los cuadernos en el editor del Query

En el cuadro del Query elegimos la hoja adecuada ("reporte" en nuestro ejemplo)

y apretamos el botón "Edit" para transferirla a la ventana de editor del Quey. Aquí no aseguramos que lo valores de la primer línea pasen a ser los encabezamientos de las columnas

En la ventana de las definiciones del Query (Query Settings) usamos la posibilidad "Load to data model" (cargar al modelo de datos). Finalmente apretamos "Apply & Close". En la hoja del cuaderno aparece ahora la ventana de los Workbook Queries, mostrando que hay una consulta (query)  con 6000 filas.
Repetimos el proceso para las otras dos sucursales. Al finalizar tendremos esta situación:

Ahora tenemos 3 consultas en el cuaderno. Para integrarlas en una única tabla vamos a usar la opción Append

Al apretar "Ok" Excel crea una nueva consulta con el nombre por defecto "Append1"

Esta nueva consulta tiene 12000 filas. El Power Query no tiene incorporada la posibilidad de combinar varios cuadernos de una vez, por lo que volvemos a repetir la operación integrando Append1 con "reporte(3)".

A diferencia de la veces anteriores, ahora usamos la posibilidad "Load to worksheet" (cargar en la hoja), por lo que obtenemos una tabla de 18000 filas

A partir de aquí todo lo que nos queda por hacer es analizar los datos combinados con las herramientas que Excel pone a nuestra disposición, principalmente tablas dinámicas.

Dos cuestiones quedan abiertas:

1. ¿qué pasa si tenemos que integrar una cantidad considerables de hojas? ¿Hay alguna manera de automatizar el proceso?
2. ¿qué hacer si las tablas no son exactamente iguales pero contienen los datos que queremos integrar?

En las próxima nota nos ocuparemos del primer tema