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.

Usos del Power Query - importar y transformar datos externos

En la nota anterior vimos cómo importar datos externos con el Power Query, más precisamente una listas de carpetas y archivos. De la misma manera podemos importar datos externos diversas fuentes como bases de datos, Web, archivos texto, archivos Excel, etc.
Power Query nos permite también transformar los datos originales antes de transferirlos a una hoja de Excel o al modelo de datos (tema que será tratado en otra nota).

Siguiendo con el ejemplo de la nota anterior la ventana del editor del Query se ve así

La ventana del editor tiene una barra de fórmulas, un área de datos (filas y columnas) y dos barras a los costados ("Navigator" y "Steps").

Como podrán apreciar, en los encabezamientos de las columnas aparece un triángulo similar al que aparece cuando aplicamos Autofiltro a una tabla en la hoja de Excel. Estos triángulos tiene la misma función en la ventana de Query

Esto nos permite filtrar las filas de la misma manera como lo hacemos con las tablas en Excel. Por ejemplo, si queremos que la venta del query muestre sólo las filas con archivos de tipo ".png", filtramos de acuerdo a este criterio

Una vez aplicado el filtro podemos apretar el botón "Done" en la ventana del query y sólo las filas filtradas serán transferidas a la hoja de Excel

Si queremos cambiar el resultado de la consulta, abrimos el editor del query usando el comando Filter & Shape

Supongamos ahora que queremos transferir sólo los archivos de tipo ".png" creados en el 2014; todo los que hacemos es aplicar un nuevo autofiltro a la columna "Date Created"

Haciendo un clic con el botón derecho del mouse podemos ver las distintas posiblidades para cada una de las columnas.  Por ejemplo, un clic en el encabezado de la columna "Content" nos muestra

Como ven podemos eliminar la columna (Remove) o eliminar todas las otras columnas (Remove other columns), duplicarla, cambiar el tipo de datos, buscar y reemplazar valores (Replace values),etc. Especialmente práctica es la función "Unpivot Columns" como ya hemos mostrado en esta nota.

Supongamos que queremos eliminar todas las columnas excepto "Name", "Extension" y "Date Created". Lo que hacemos es seleccionar estas columnas (clic sobre el encabezamiento manteniendo el botón Ctrl apretado) y activar la opción "Remove other columns"

Ahora vamos a explorar las posibilidades de transformación de los datos de la columna "Date Created". Con un clic del botón derecho del mouse abrimos el menú contextual

Debido al tipo de datos de la columna, la función "Transform" nos muestra las distintas posibilidades de transformación: fecha (Date), hora (Time), día (Day), etc. Por ejemplo, si elegimos la opción "Date", las fechas aparecerán sin las horas.

Si expandimos la barra "Steps" a la derecha de la ventana del Query, podemos ver los pasos que hemos realizado

Cuando apuntamos a una de las acciones aparece una X. Un clic sobre la X cancela la operación, como la acción "Deshacer" (undo, Ctrl Z) en Excel.

En la próxima nota veremos más usos de las posibilidades de transformación de datos con Power Query.