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