martes, 9 de octubre de 2018

Power Query: Filtro de datos dinámico

Explicaré hoy una utilidad alternativa al filtro avanzado empleando Power Query (Obtener y Transformar), en concreto aplicaremos un filtro múltiple sobre una base de datos fuente, con la ventaja de controlar desde la hoja de cálculo las condiciones del filtro.

Tenemos una base de datos origen con Zona, años e importes (llamada 'TblDatos'), y una segunda tabla con las condiciones a aplicar (llamada 'TblFiltros').

Power Query: Filtro de datos dinámico


En primer lugar cargaremos y guardaremos la primera tabla 'TblDatos' sobre la hoja de cálculo, por ejemplo en la celda F7.
Así pues desde la ficha Datos > grupo Obtener y transformar > botón Desde una Tabla cargamos en el editor de Consultas la información.
A continuación Cerramos y guardamos en la hoja de cálculo en la celda F7 comentada.


Cargamos solo como conexión la 'TblFiltros'.
Y a continuación repetiremos en tres ocasiones la siguiente acción (una por cada criterio de filtro a aplicar).
Una vez cargada la consulta de filtros desde la ficha Datos > grupo Obtener y transformar > botón Desde una Tabla, haremos clic derecho sobre la consulta en cuestión, marcando la opción de Referencia o Duplicar:

Power Query: Filtro de datos dinámico


Recuerda repetir la acción tantas veces como filtros distintos desees aplicar.

En la siguiente etapa seleccionaremos uno a uno estas consultas duplicadas e iremos a Rastrear desagrupando datos para cada una de los valores de los filtros.
Esto lo haremos marcando el dato en la tabla a la vista y haciendo clic derecho.

Power Query: Filtro de datos dinámico


Lo haremos igual para la celda de la Zona, del Año y del Importe, una por cada una de las consultas duplicadas...
Para facilitar su uso posterior renombraremos estas 'consultas' como varZona, varAño y varImporte.

Power Query: Filtro de datos dinámico



Última fase.
Volvemos a la consulta de la TblDatos (la tabla principal) en nuestro editor de consultas de Power Query... y aplicaremos los filtros particulares que deseemos sobre los campos en cuestión...
NO importa qué filtros sean, ya que en el siguiente paso los personalizaremos empleando nuestras recién creadas variables.

Entonces, desde nuestra consulta aplico:
1- un filtro sobre el campo Zona para que muestre la zona Sur,
2- un filtro sobre el campo Año para que muestre el año 2019
3- y un filtro sobre el campo Importe para que muestre importes menores o iguales a 3000

Si accedemos al Editor avanzado de nuestra consulta veremos:
let
    Origen = Excel.CurrentWorkbook(){[Name="TblDatos"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Zona", type text}, {"Año", Int64.Type}, {"Importe", Int64.Type}}),
    #"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([Zona] = "Sur") and ([Año] = 2019)),
    #"Filas filtradas1" = Table.SelectRows(#"Filas filtradas", each [Importe] <= 3000)
in
    #"Filas filtradas1"

Power Query: Filtro de datos dinámico

Ahora solo buscaremos los elementos concretos grabados y los sustituiremos por nuestras variables: varZona, varAño y varImporte
let
    Origen = Excel.CurrentWorkbook(){[Name="TblDatos"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Zona", type text}, {"Año", Int64.Type}, {"Importe", Int64.Type}}),
    #"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([Zona] = varZona) and ([Año] = varAño)),
    #"Filas filtradas1" = Table.SelectRows(#"Filas filtradas", each [Importe] <= varImporte)
in
    #"Filas filtradas1"
y presionamos Listo
ya veremos el resultado listado de acuerdo a los criterios de nuestra hoja de cálculo... Pero para acabar presionaremos Cerrar y Cargar y visualizaremos el resultado en nuestras celdas.

Power Query: Filtro de datos dinámico

Comprueba que cambiando los criterios de esa fila 2 de la TblFiltros y tras actualizar los datos, la tabla resultante refrescará con la información correcta. Un dato final... la primera vez que se ejecute la consulta se abrirán tantas hojas como consultas duplicadas hayamos creado... todas ellas se pueden eliminar son problemas (es recomendable para no ralentizar nuestro fichero).

2 comentarios:

  1. Muy interesante y muy bien explicado.
    Una duda, si una vez que hemos realizado todos los pasos descritos, queremos que se apliquen los criterios de Zona e importe pero que nos aparezcan todos los años, que tenemos que poner como criterio en Años?
    Una opción sería quitar el criterio de los años, pero sin hacerlo, sería posible? Mas de una vez me surge que por el motivo que sea tengo que dejar un criterio sin aplicar, pero no quiero tener que modificar todo el código.

    ResponderEliminar
    Respuestas
    1. Hola Adolfo,
      en este caso concreto se me ocurre cambiar el campo Año a texto (type text) para así aplicarle luego un criterio de Contains... si quieres mostrar todos los años basta poner en la celda un 2, el código cambiaría en la fila:
      #"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([Zona] = varZona) and (Text.Contains([Año], Number.ToText(varAño,"G",""))))

      por desgracia, hasta donde yo se, no existen comodines o similares..

      Espero te sirva

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.