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.

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

Usos del Power Query - combinar datos de fuentes distintas

En las notas anteriores sobre los usos posibles del Power Query vimos como podemos aprovechar esta herramienta para "aplanar" datos (formato más conveniente para analizarlos con tablas dinámicas), importar datos de diversas fuentes y transformar los datos importados.

En este post vamos a mostrar cómo podemos combinar datos de distintas fuentes con facilidad usando el Power Pivot. Supongamos el siguiente escenario:

• en un archivo Excel tenemos una lista de productos con los campos: número de catálogo, descripción, cantidad vendida e importe;
• en otro archivo de Excel tenemos una lista que incluye el costo por unidad de los productos.

Nuestro objetivo es calcular la ganancia bruta (venta - costo de producción).

Antes de la aparición del Power Query y dependiendo del nivel del usuario hubiéramos completado la tarea con alguna de estas técnicas:

1. abrimos los dos cuadernos; en el cuaderno de ventas traemos el costo de cada producto usando BUSCARV o INDICE;
2. usando MsQuery; combinamos las hojas de los cuadernos e el MsQuery, creamos la consulta y la transferimos a una hoja de Excel;
3. usando Access; creamos dos tablas en una base de datos de Access importando los datos de los cuadernos de Excel, creamos una consulta y la exportamos a Excel.

Power Query permite realizar la tarea en forma integral y sencilla, sin necesidad de abrir los cuadernos. En el ejemplo que voy a desarrollar tenemos dos archivos: "Ventas" y "Costo por unidad".

Empezamos por cargar el archivo "Ventas" en la ventana de edición del Power Query. En el grupo "Get external data" (datos externos) de la pestaña del Power Query elegimos "From file--From Excel"

En la ventana de diálogo que se abre elegimos el archivo indicado; al apretar "Ok" se abre la ventana de navegación del Query en la hoja de Excel

En la ventana vemos el icono del cuadernno y uno por cada hoja que contenga. En nuestro ejemplo hay una única hoja. Para cargar la hoja elegida en la ventana del Query apretamos "Edit". Esto nos permitirá realizar cambios y transformaciones en los datos y columnas. En caso de no necesitar realizar ninguna operación sobre los datos podemos apretar "Load", lo que hará que los datos sean transferidos directamente a la hoja de Excel.

En nuestro caso elegimos "Edit" para poder definir que la primera fila sea los encabezados de las columnas

En la sección derecha de la ventana del Query podemos dar un nombre y una descripción al Query (será útil en los próximos pasos), la sección "Applied Steps" (que hemos mencionado en la nota anterior) y la sección "Load Settings" donde podemos elegir entre cargar los datos a una hoja de Excel o al modelo de datos ("Data Model", tema que trataremos en futura nota). En este ejemplo cargamos los datos en la hoja apretando "Apply and Close"

Repetimos el proceso para el archivo con el costo de los productos. Al final del proceso, cuando activamos alguna de las hojas donde hemos transferidos los datos veremos una ventana que nos muestra los queries existentes en el cuaderno.

Ahora podemos combinar las tablas. Para esto usamos la opción Merge en el menú del Power Query lo que abre el diálogo del Merge

En la primer parte del Merge elegimos la tabla de Ventas y señalamos la columna "Producto" que es el campo en común con la tabla de Costos por Unidad; en la segunda parte ponemos, obviamente, la tabla Costo por Unidad y señalamos el campo Producto; marcamos la opción "Only iclude matching rows". Apretamos "Ok" y Excel abre la ventana del editor del Query con los datos combinados.

Como puede apreciarse la a segunda tabla aparece "concentrada" en una única columna con el encabezado "New Column". Podemos expandir y seleccionar qué columnas queremos que aparezcan en la tabla combinada apretando la doble flecha a la derecha de "New Column"

Como la columna Producto ya aparece en la tabla de venta, seleccionamoos sólo Costo por Unidad. Cambiamos el encabezado de la columna a "Costo por Unidad"

Ahora queremos calcular el costo de cada venta, para lo cual tenemos que multiplicar el Costo por Unidad por la Cantidad. Lo hacemos insertando una columna Custom

La forma de construir la columna calculada es bien intuitiva: en la columna "Available columns" seleccionamos "Cantidad" con un doble clic; agregamos * para indicar la multiplicaciòn y con otro doble clic agregamos "Costo por Unidad"; finalmente apretamos Ok.

Por defecto Excel pone el encabezado "Custom", que podemos cambiar a "Costo Total". Ahora podemos agregar una nueva columna: Ganancia bruta (Importe - Costo Total)

Este es el resultado

Todo lo que nos queda es aplicar y cerrar (Apply and Close) lo que transfiere la consulta a la hoja de Excel y de aquì en adelante podemos usar tablas dinámicas o cualquier otra arma de Excel para analizar y resumir los datos

Ahora supongamos que el responsable de los costos nos informa que ha corregido los costos en el cuaderno Costo por Unidad. Todo lo que tenemos que hacer es aplicar "Refresh" (Actualizar) y luego aplicar refresh a la tabla combinada.

Este video demuestra es proceso