Reportes dinámicos con Power Query

La introducción de las nuevas herramientas de Excel, Power Query y PowerPivot, han facilitado enormemente la tarea de analizar datos y crear reportes. La evolución que comenzó hace veinte años con las tablas dinámicas, ha terminado por convertir a Excel en una verdadera herramienta de BI (Business Intelligence).
Hoy en día Excel puede "digerir" cualquier cantidad de datos de prácticamente cualquier origen. Los analistas de datos sabemos que la principal dificultad es crear un reporte dinámico que sea "a prueba de balas" aun en manos del más insoportable de los gerentes de nuestra compañía.

Veamos el siguiente escenario: a partir de los datos de ventas (en nuestro ejemplo usaremos la consulta Invoices de la base de datos Northwind) creamos una reporte que muestra las ventas de los 10 principales clientes de la empresa. Después de volcar los datos en una hoja de Excel (o crear una conexión a la base de datos), creamos una tabla dinámica

y la filtramos con la posibilidad Filtros de Valor-Diez mejores

Diez minutos después de haber enviado el reporte nuestro jefe nos preguntará qué tiene que hacer para ver 25 clientes en lugar de 15.
Aquí comienza nuestro dilema. Una posibilidad es explicarle al jefe como cambiar los valores del filtro. La pregunta contiene la respuesta: si supiera como hacerlo no lo hubiera preguntado y si no lo sabe la probabilidad que entienda la explicación y no arruine el reporte es mínima.
La solución es crear un mecanismo tal que al introducir un número en una celda de la hoja, la cantidad de clientes en el reporte cambie, como en este ejemplo

Una posibilidad es usar una macro que tome el valor de la celda C3 y lo use como variable para cambiar el valor del filtro. En este ejemplo, en lugar de macros, estoy usando el valor de la celda C3 para cambiar la consulta hecha con el Power Query a la base de datos.
Veamos como construir el modelo paso por paso.
Empezamos por crear una consulta a la base de datos (en nuestro ejemplola base Northwind en Access). Las transformaciones que hacemos con Power Query pueden verse en este video



Después de crear la conexión, agrupamos las filas por cliente creando un campo que totaliza la ventas por cliente; ordenamos las filas en orden decreciente por total de ventas; agregamos una columna Índice que nos servirá de indicador; eliminamos las columnas agrupadas y expandimos "Todas Filas" de manera que volvemos a tener todas las columnas originales más el índice; finalmente filtramos la consulta con el criterio "<=15".

El segundo paso es crear una conexión a la celda C3 para poder controlar la cantidad de filas filtradas. La clave aquí es crear un nombre definido que se refiera a la celda. La forma más práctica es seleccionar la celda y reemplazazr la referencia en el cuadro de nombres por un nombre, en nuestro ejemplo "TopCust"

Ahora usamos la opción "Obtener datos - desde una tabla o rango" y en la consulta creada usamos "Rastrear desagrupando datos" (Drill Down)

con este resultado

La consulta, que recibe el nombre del nombre definido (TopCust), la guardamos creando sólo una conexión.

Ahora volvemos a editar la primer consulta y en el último paso, Filas Filtradas, reemplazamos el valor fijo por la segunda consulta "TopCust"

Ahora, al cambiar el valor de la celda C3 (TopCust) al apretar "Actualizar todo" el modelo se actualizará

Un detalle crítico es deshabilitar las actualización en segundo plano, como muestro en este post.

Si queremos que nuestro modelo sea totalmente dinámico podemos agregar una evento (macro) de manera que al cambiar el valor en la celda C3, se ejecute el método RefreshAll. En el módulo de la hoja ponemos este evento

 Private Sub Worksheet_Change(ByVal Target As Range)  
   If Target.Address = Range("TopCust").Address Then ThisWorkbook.RefreshAll  
 End Sub