En el post de hoy vamos a ver una técnica para hacer filtros en Power Query de manera dinámica a través de una lista.
¿En qué escenarios nos puede ser útil?.
Por ejemplo, si tenemos un grupo de empresas y queremos montar un informe de ventas para algunas de ellas. O si tenemos varios almacenes y queremos realizar un informe de rotaciones, ventas o compras para ciertos almacenes (y que podamos cambiar el filtro de manera dinámica en una sola consulta).
Tendremos en Power Query una lista con las empresas/almacenes objeto del análisis y, si modificamos esa lista, se modificarán los filtros que se aplicarán en nuestras consultas.
Veámoslo con un ejemplo.
Supongamos que estamos elaborando un informe de ventas para nuestro grupo empresarial.
En primer lugar, nos conectamos a la base de datos del ERP, concretamente a la tabla que contiene el maestro de empresas, y filtramos las empresas de las que queremos hacer el informe (en nuestro ejemplo, las empresas 1 y 4):
Posteriormente, para ya dejar preparada nuestra dimensión empresas en el modelo, nos quedamos únicamente con los campos CodigoEmpresa y Empresa (que contiene el nombre de la sociedad):
A continuación, vamos a hacer referencia a esta consulta dimEmpresas para generar una nueva consulta con la que vamos a generar la lista que nos va a servir para filtrar todos los datos del resto de consultas.
En la nueva consulta, nos vamos a quedar únicamente con la columna “CodigoEmpresa” y, desde el menú “Transformar” vamos a elegir la opción “Convertir en lista”:
Cambiamos el nombre de la consulta a “Lista empresas” y la consulta quedaría de la siguiente forma (vemos que el icono de la consulta ha cambiado de un icono de tabla a un icono de lista):
Ahora, para construir el modelo de ventas, vamos a imaginar que vamos a necesitar conectarnos, entre otras, a estas 6 tablas: CabeceraOfertaClientes, LineasOfertaClientes, CabeceraPedidoClientes, LineasPedidoClientes, CabeceraAlabaranClientes y LineasAlbaranClientes.
En cada una de las consultas, tendremos que filtrar los datos por el campo CodigoEmpresa, seleccionando únicamente las empresas 1 y 4.
Lo que vamos a conseguir con la técnica explicada en este post, es dejar vinculado ese filtro a nuestra consulta “Lista empresas”, de manera que si cambiamos el filtro de las empresas elegidas en la consulta “dimEmpresas”, como la consulta “Lista empresas” está referenciada a “dimEmpresas”, automáticamente cambien las empresas que se filtran en las 6 tablas relacionadas con las ventas.
Vamos a ver el ejemplo con la tabla “CabeceraOfertaCliente”.
Nos conectamos a la tabla y nos aparece su información:
Ahora, si como planteábamos anteriormente, nuestro informe queremos que incluya únicamente a las empresas 1 y 4, iríamos al campo “CodigoEmpresa” y filtraríamos por los valores 1 y 4 como mostramos en la siguiente imagen:
El código que nos genera este paso de filtrado es el siguiente:
= Table.SelectRows(dbo_CabeceraOfertaCliente, each ([CodigoEmpresa] = 1 or [CodigoEmpresa] = 4))
Lo que pasa es que el filtro se nos queda fijado de forma estática a las empresas 1 y 4.
Si quisiésemos más adelante cambiar las empresas filtradas, tendríamos que venir a este paso y modificarlo (en cada una de las consultas).
Lo que vamos a hacer, es que este filtro de los códigos de empresa dependa de la lista que hemos creado previamente, para que nos quede filtrado de forma dinámica por la lista creada: “Lista empresas”.
Para ello, tocaremos el código M de este paso de filas filtradas, y pondremos el siguiente código:
= Table.SelectRows(dbo_CabeceraOfertaCliente, each List.Contains(#”Lista empresas”, [CodigoEmpresa]))
Lo que estamos haciendo en el código, es indicar que el campo “CodigoEmpresa” se filtre por los valores que estén contenidos en la lista “Lista empresas”.
Si volvemos ahora al campo “CodigoEmpresa” y desplegamos la flecha del filtro, vemos que están únicamente disponibles las empresas de la lista, la 1 y la 4:
Esta técnica de filtrado la usaríamos con el resto de tablas a incorporar al modelo de ventas: LineasOfertaClientes, CabeceraPedidoClientes, LineasPedidoClientes, CabeceraAlabaranClientes y LineasAlbaranClientes….
De esta forma, modificando la selección de las empresas a incorporar en el informe únicamente en la consulta “dimEmpresas”, conseguiremos modificar los códigos de empresa que están incluidos en la lista “Lista empresas” y, con ello, las empresas que se filtran en el resto de consultas que dependen de esa lista.
Espero que te sirva para tus futuros proyectos!!!
Seguimos 👊