Filtros inteligentes en Power BI / Power Query con operaciones de combinación

¿Alguna vez has querido aplicar un tipo específico de filtro que podría resultar demasiado específico al hacerlo solo con el desplegable del filtro regular o incluso con las operaciones avanzadas de filtro en Excel?

Déjame darte un ejemplo de uno de esos escenarios. Imagina que tienes una tabla como la siguiente:

Project Person Task
Powered Solutions Miguel All
Powered Solutions Alonso Sales
PowerQuery.Training Miguel All
PowerQuery.Training Ken All
PowerQuery.Training Rebekah Marketing / Support
Secret Project Miguel All
Secret Project Jean All
2nd Edition Book Ken Writer
2nd Edition Book Miguel Writer

y la idea es que deseas tener todas las filas de todos los proyectos de los que formó parte la Persona X. Por ejemplo, quiero ver todas las filas de todos los proyectos en los que estaba Ken. Eso daría el siguiente resultado:

Project Person Task
PowerQuery.Training Miguel All
PowerQuery.Training Ken All
PowerQuery.Training Rebekah Marketing / Support
2nd Edition Book Ken Writer
2nd Edition Book Miguel Writer

Además, quiero poder cambiar dinámicamente la persona. Quizás no quieras ver la información de Ken, sino de mí mismo (a Miguel) o de cualquier otra persona. Similar a lo que ves en el siguiente video:

Entonces, ¿cómo podemos hacer eso con Power BI / Power Query?

Antes de empezar: prepara todas las piezas

Para que esto funcione, primero deberás tener listas todas las piezas de esta receta. La primera es la tabla real de origen, que se ve así en Power BI Desktop:

Puedes hacer clic en el botón de abajo para descargar el archivo que he usado y que tiene los datos incrustados:

Descargar Archivo PBIX

A partir de esto, tendremos que hacer lo siguiente:

  • Crear una consulta que cree dinámicamente una lista de “Employees” (Empleados) únicos
  • Crear un Parámetro que podamos usar como selector y también como entrada para otras funciones
  • Crear una tabla de búsqueda para todos los proyectos a los que está asociada la persona ‘X’ (que se ha tomado del parámetro creado anteriormente)
  • Crear un filtro inteligente con una operación de combinación para obtener nuestro resultado

Ahora que tenemos una hoja de ruta, abordemos esto.

Crear una consulta de lista dinámica de empleados únicos

Este es bastante sencillo. Desde nuestra consulta original, simplemente haz clic derecho en la columna Empleado y selecciona la opción que dice “Add as a New Query” (Agregar como una nueva consulta).

Como dice la etiqueta, esto creará una nueva consulta que generará una lista con todos los valores de esa lista.

Como no queremos duplicados, podemos continuar y hacer clic en la opción dentro del menú que dice “Remove Duplicates” (Eliminar duplicados):

¡Y ya está! Acabas de crear la consulta que necesitábamos. ¡Vamos a pasar a la siguiente pieza!

Crear un Parámetro

Esto es bastante fácil también. En el menú Inicio, selecciona la opción que dice “Manage Parameters”  (Administrar parámetros) y en el menú desplegable, selecciona la opción para crear un “New Parameter” (Nuevo parámetro).

Aparecerá una nueva ventana donde puedes definir tu nuevo parámetro. Así es como debería verse:

Toma en cuenta que donde dice “Suggested Values” (Valores sugeridos), podemos seleccionar la opción para usar una consulta como los valores sugeridos y ahí es donde aparece nuestra consulta “Employee” (Empleado) creada anteriormente, ya que siempre nos da la lista única de todos los empleados.

¡Y eso es todo! Acabas de crear tu Parámetro para hacer que las cosas sean dinámicas y más fáciles para el usuario final. Vayamos al siguiente paso.

Creación de una tabla de búsqueda para el filtro inteligente

Lo primero que vamos a hacer es simplemente hacer referencia a la consulta original.

En la consulta recién creada, crearemos toda la magia que luego caerá en cascada a las otras consultas. Antes de continuar, pongámosle como nombre LookUpProjects.

Esta consulta referenciada no es más que la copia de la salida de la consulta de Proyectos. En este caso, vamos a hacer un filtro en la columna Empleado utilizando el valor derivado del Parámetro que creamos. Simplemente selecciona la opción del filtro de columna y elije la que dice “equals” para el texto. Esto te mostrará una nueva ventana donde puedes seleccionar el parámetro como la entrada y debería verse así:

Ahora podemos pasar a la última fase, que es simplemente crear el filtro inteligente en sí mismo.

Realizar un filtro inteligente

Image result for macgyver
  • Facebook
  • Twitter
  • LinkedIn

Si has  conversado conmigo anteriomente sobre Power Query, lo más probable es que te habría convencido sobre el hecho de que Power Query (y la preparación de datos en general) nos permite acercarnos a lo que MacGyver hacía en su programa de televisión: encontrar formas inteligentes para resolver problemas con las herramientas que tenemos disponibles para ser lo más ingeniosos que podamos.

This is exactly what this blog post is about – nothing more than being resourceful and finding a clever way to make things happen in the most efficient way possible.

Esto es exactamente de lo que se trata esta publicación, nada más se trata de ser ingenioso y encontrar una manera inteligente para hacer que las cosas sucedan de la manera más eficiente posible.

Antes de hacer esto, avancemos y hagamos otra referencia en la consulta original de Projects y cambiemos el nombre de esta nueva consulta a Output.

Lo que llamo un filtro Inteligente no es más que comprender a profundidad cómo funcionan las Operaciones de Combinación (o JOINS) dentro de Power Query y cómo puedes usar esas operaciones a tu favor. Todo el mundo sabe cómo VLOOKUP puede llevar un valor de una matriz a una celda específica, pero, ¿qué sucede si no se encuentra esa celda y simplemente has eliminado la fila actual? Bueno, eso es exactamente lo que sucede con la siguiente operación de combinación.

de forma predeterminada, obtenemos la combinación izquierda externa, pero si seleccionamos la externa derecha, obtenemos todas las de la segunda (consulta LookUpProjects) que también coinciden con la consulta de salida.

El resultado de esto se ve así:

¡Y eso es exactamente lo que estamos buscando!

En realidad te digo que el usuario promedio de Power Query podría grabar tantos episodios sobre reparación de datos como el programa original de MacGyver y probablemente aún más.