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

Transponer rangos que contienen referencias

Excel facilita la tarea de transponer rangos, es decir, invertir el orden filas/columnas. Todo lo que hay que hacer es seleccionar el rango, copiarlo (Ctrl+C) y pegarlo en otra área usando la opción Transponer de Pegado Especial.
El proceso funciona sin problemas si el rango a transponer contiene sólo valores

y por lo general también si el rango contiene fórmulas. En el ejemplo de arriba, la celda C3 (Enero/Norte) contiene la fórmula

=SUMAPRODUCTO((Mes=$B3)*(Sucursal=C$2)*Ventas)

donde "Mes", "Sucursal" y "Ventas" son nombres definidos que se refieren a los rangos que contienen los datos.

En la tabla transpuesta las ventas de Enero de la zona norte se encuentran en la celda C13 que contiene la fórmulas

=SUMAPRODUCTO((Mes=C$12)*(Sucursal=$B13)*Ventas)

Pero hay casos en que Excel puede dar resultados incorrectos como en este ejemplo. Los datos en el rango D2:E6 se refieren a los datos del rango B2:B11

El valor en D2 está ligado a B2; E2 a B7; D3 a B3; etc.

Dado que las referencias son relativas, al transponer el rango Excel "reordena" las referencias

Podemos transponer copiando sólo lo valores (Pegado Especial-Valores-Transponer), pero si queremos conservar las referencias/fórmulas podemos aplicar esta técnica que publicó Chandoo en su blog:

1. Seleccionamos el rango a transponer (D2:E6 en nuestro ejemplo);
2. usamos Buscar y Reemplazar (Ctrl+L) para reemplazar los "=" por "#"
3. copiamos (Ctrl+C) y transponemos el rango modificado;
4. en el rango transpuesto reemplazamos los "#" por "="

Al reemplazar los "=" por "#", las referencias se convierten en constantes; al reemplazar los "#" por "=" en el rango transpuesto, convertirmos las constantes nuevamente en referencias. Este video muestra el proceso

Este método es útil sólo si los símbolos "=" aparecen al principio de la referencia/fórmula.