Generar reportes con Power Query

Power Query, o Datos-Obtener y Transformar para los usuarios de Excel 2016, es más que una herramienta para obtener, transformar y cargar datos. También podemos usarlo como herramienta para generar informes. La clave reside en la herramienta “Agrupar por” del editor de Power Query.

Veamos un ejemplo, basado en la base de datos Northwind (como de costumbre). Nos piden un informe que el promedio de cada operación de venta del año 2015, por agente. Nuestra base de datos contiene las ventas por cliente, agente, orden de venta y fecha.

Creamos una consulta en el editor de Power Query que, después de hacer las operaciones "de limpieza" necesarias, se ve así

Creamos el reporte con los siguientes pasos:

Empezamos por abrir el menú “Agrupar por”

En el formulario que se abre definimos

• en "Agrupar por" elegimos la columna "Agente";
• en "Nuevo nombre de columna" cambiamos el nombre de la  casilla (al abrir el formulario sólo se ve una casilla) a "Operaciones" y en la casilla "Operación" elegimos "Contar filas distintas" (traducción poco exitosa, en mi opinión, de Distinct Count);
• usamos el símbolo + (a la derecha de "Columna") para agregar otro campo y en el nombre ponemos "Ventas", en la operación "Suma" y elegimos la columna Ventas para la operación.

De esta manera creamos una tabla con tres columnas: Agente, Operaciones y Ventas

Ahora podemos crear una columna calculada para el promedio de venta por agente

Este video muestra el proyecto

Todo los que no queda por hacer ahora es cargar la consulta a una hoja de Excel, dar un buen formato a los números y..listo.

Listas Desplegables con valores únicos con Power Query

En el pasado remoto de este blog publiqué dos posts sobre cómo crear una lista desplegable con valores únicos a partir de una tabla de datos. El escenario habitual es una tabla de datos, supongamos de ventas, a partir de la cual queremos crear una validación de datos de los nombres de los clientes.
El procedimiento habitual sería crear un nombre definido que se refiera a la columna que contiene los nombres de los clientes. El problema es que los nombres se repiten y lo queremos es una liksta de valores únicos.

El primer post, del año 2006, presentaba una solución compleja con fórmulas. Hasta tal punto compleja que publiqué un post adicional para explicar el funcionamiento de esta fórmulas.

El segundo post, del año 2011, presentaba una solución tal vez más elegante pero también compleja, incluyendo el uso de Vba (macros).

Usando Power Query  podemos construir una solución más sencilla y ésto es lo que mostraré en este post.

El escenario:

• una hoja que contiene una tabla con datos de ventas;

• una hoja donde queremos queremos construir una reporte sencillo: una celda que contiene el nombre del cliente, que se elige de una validación de datos y un celda donde calculamos las ventas del cliente com SUMAR.SI, basándonos en el nombre del cliente seleccionado.

donde la celda C3 contiene la fórmula 

=SUMAR.SI(tbl_Sales2016[Cliente],B3,tbl_Sales2016[Monto])

Primer paso: crear una consulta en el Power Query para generar una lista de valores únicos ordenados alfabéticamente.

Segundo paso: crear un nombre definido que se refiera al rango de los nombres de los clientes y crear la lista desplegable con validación de datos.

La gran ventaja de este modelo sobre las soluciones anteriores es que una vez construida la consulta sólo tendremos que procuparnos de actualizar la consulta cada vez que agreguemos o quitemos filas en la tabla de datos (clientes nuevos o clientes que dejan de estar en a lista).

La desventaja es que tenemos que actualizar la consulta! Es decir, tendremos que confiar que el usuario no se olvide de hacerlo.

Podemos evitar este inconveniente creando un evento de manera que cada vez que el usuario sale de la hoja "bd" la consulta se actualice. En el módulo del editor de Vb de la hoja "bd" creamos este evento Worksheet_Deactivate

 De esta manera nos aseguramos que la lista deplegable se actualizará con cada cambio en la tabla de datos.

Unir hojas de un archivo Excel con Power Query

Casi todo usuario de Excel se ha enfrentado en algún momento a la tarea de unir datos de distintas hojas de un cuaderno. Por ejemplo, en un cuaderno Excel tenemos cuatro hojas conteniendo cada una los datos de ventas de cada uno de los trimestres del año. De la misma manera podríamos tener un cuaderno con 12 hojas, una por cada mes; o 48 hojas, una por cada mes de los últimos dos años; o…bien, la idea queda clara.

Si se trata de unir dos o tres tablas, que tienen la misma estructura, un simple Copiar y Pegar nos resuelve el problema. Pero cuando tenemos un número considerable de hojas Copiar y Pegar no sólo es tortuoso sino que puede conducir a omisiones o errores.

Power Query nos permite hacerlo con unos pocos clics y sin necesidad de abrir el archivo, de manera que podemos crear un nuevo archivo sin alterar el original.

Supongamos que nuestro original contiene cuatro hojas con los datos de ventas de cada uno de los trimestres del año. En la pestaña del Power Query (en Excel 2010/13, Datos Obtener y Transformar en 2016) creamos una consulta 

En la ventana del Navegador podemos ver las cuatro hojas del cuaderno. En lugar de seleccionar las hojas una por una, hacemos un clic al nombre del archivo (Ventas anuales por trimestre.xlsx) y apretamos el botón Edit. El resultado es el siguiente

Filtramos la tabla dejando visible sólo los valores “Sheet” en la columna “Kind”

con este resultado

Ahora eliminamos todas las columnas excepto “Data” (opcionalmente podemos dejar también la columna “Name” para identificar el origen de los datos) y apretamos el botón Expand (la doble flecha a la derecha del encabezado de la columna)

No nos dejamos inquietar por la advertencia “List may be incomplete” y apretamos “OK” con decisión. 

Como podemos apreciar, los encabezamientos aparecen en la primer fila por lo que deberemos promoverlos con “Use first row as headers”. Ahora se nos presenta un último problema: cada tabla en cada hoja cuenta con encabezamientos por lo que éstos aparecen en las filas de la tabla unificada y debemos eliminarlos.

Para hacerlo usamos el filtro en alguna de las columnas, por ejemplo en la primera quitamos la marca de "Customers.CompanyName"

Todo lo que nos queda por hacer es apretar Close and Load y ya tenemos nuestros datos integrados en una única tabla.