Usos del Power Query - integrar archivos de una carpeta

La nota anterior de la serie terminaba con una (casi existencial) pregunta:

¿qué pasa si tenemos que integrar una cantidad considerables de hojas? ¿Hay alguna manera de automatizar el proceso?

En la nota vimos como integrar datos de tres archivos en una única tabla usando el Power Query; pero si el número de archivos a integrar es considerable, es natural que busquemos una forma menos "pedestre" de hacerlo.

Las buena noticia es que se puede; la mala es que no es trivial. No sólo no es trivial sino que para hacerlo deben cumplirse ciertas condiciones "sine qua non" (por lo menos hasta donde dan mis conocimientos):

1. los archivos/cuadernos deben estar en la misma carpeta;
2. las tablas de datos deben tener exactamente la misma estructura (campos y orden);
3. la hoja con los datos en cada cuaderno debe tener el mismo nombre. 

A partir de aquí esta nota se basa en este post del excelente blog Bacon Bits de Mike Alexander.

Iré mostrando el proceso paso por paso, con explicaciones lo menos técnicas posible donde sea necesario.

En nuestro ejemplo tenemos tres cuadernos Excel datos de ventas de tres sucursales (Norte, Sur y Centro); cada cuaderno contiene una única hoja con el nombre "reporte".

Empezamos por crear una consulta a uno de los cuadernos

Como mostramos en las notas anteriores, seleccionamos la hoja "Reporte" y usamos la opción Edit para abrir la consulta en la ventana del editor de Query

En la ventana del Query usamos la opción "Use first row as headers"

En la imagen arriba podemos apreciar que, similar a lo que conocemos de una hoja de Excel, existe una barra de fórmulas. Despues de convertir la primer fila de la hoja en encabezamiento de la tabla podemos ver que Query a aplicado la función Table.PromoteHeaders

Para combinar, integrar y transformar datos de diversas fuentes Power Query usa un lenguaje de fórmulas informalmente llamado "M". Por el momento no vamos a agregar nada más sobre el tema; sólo recordemos que Power Query usa fórmulas.

Nuestro próximo paso consiste en abrir el editor avanzado del Query (View-Advanced Editor)

Como puede verse, Power Query ha ido creando un código con las operaciones que hemos realizado (algo así como una macro grabada). La idea ahora es buscar la forma de reemplazar la referencia  C:\Ventas\Sucursal Centro.xlsx por una referencia variable.
Para lograr esto vamos a crear una función con dos variable "FilePath" y "FileName" (aclaración: no es obligatorio el uso de inglés para los parámetros o para el nombre de la función).

Modificamos el código en el editor avanzado agregando la función repVentas(FilePath,FilesName), y haciendo los cambiosseñalados en la imagen de abajo

Cerramos el editor avanzado; en el editor del Query le damos un nombre representativo a la funcion

y finalmente apretamos "Apply & Close" con este resultado

Lo que acabamos de hacer es crear una función en "M" que nos permitirá combinar los cuadernos que se encuentren el una misma carpeta (y que cumplas las condiciones que señalamos al principio de esta nota)

En la pestaña del Query, usamos la opción From Folder

Ingresamos el path de la carpeta que contiene los archivos y apretamos Ok. El query abre esta consulta

Abrimos el menú contextual con un clic del botón derecho sobre alguna de las columnas y eligimos la opción Insert Custom Colum; en el diálogo invocamos la función que creamos (funcVentas)

Al apretar Ok, Query agrega una nueva columna "Custom"

Ahora expandemos la columna Custom apretando la doble flecha que aparece en el encabezamiento

Aquí podemos decidir que columnas expandir y cuales no. En nuestro ejemplo expandemos todas. Antes de transferir la consulta a una hoja de Excel, eliminamos las columnas innecesarias y apretamos Apply & Close. Excel abre la tabla en una hoja

¡Misión cumplida! Como dicho, no es trivial, pero excelente herramienta si tenemos que integrar cantidades considerables de archivos.