Clever Filters in Power BI / Power Query with Merge Operations

Power BI Clever Filters

Have you ever wanted to do a specific type of filter that might be too specific to do with just the regular filter drop down or even the advanced filter operations in Excel?

Let me give you an example of one of those scenarios. Imagine that you have a table like the following:

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

and the idea is that you want to have all of the rows for all of the projects where Person X was part of. For example, I want to see all of the rows for all of the projects where Ken was in. That would yield the following result:

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

Furthermore, I want to be able to change dynamically who should be the person. Perhaps I want to see not Ken, but myself (Miguel) or any other person. Similar to what you see in the following video:

So, how do we do that with Power BI / Power Query?

Before we start: Laying down all the pieces

To make this work, you’ll first need to lay down all of the pieces of this recipe. The first one is the actual Source Table which looks like this inside of Power BI Desktop:

image

You can click on the button below to download the file that I’ve used which has the data embedded into it:

Download PBIX file

From this, we’ll need to do the following:

  • Create a Query that Dynamically creates a list of unique “Employees”
  • Create a Parameter that we can use as a selector and also as the input for other functions
  • Create a LookUp Table for all the projects that Person ‘X’ (sourced from the previously created parameter) is associated with
  • Do a Clever Filter with a Merge Operation to get to our result

Now that we have ourselves a roadmap, let’s tackle this.

Creating a Dynamic List Query of unique employees

This one is quite straightforward. From our original query, just right click on the Employee column and select the option that reads “Add as a New Query”.

image

As the label says, this will create a new query which will yield a list with all the values from that list.

Since we don’t want any duplicates, we can go ahead and click on the option inside the menu that reads “Remove Duplicates”:

image

and that’s it! You’ve just created the query that we needed. Let’s move on to the next piece!

Creating a Parameter

This one is quite easy as well. From the Home menu select the option that reads “Manage Parameters” and from the drop-down menu select the option to create a “New Parameter”.

image

A new window will pop up where you can define your new Parameter. This is how it should look like:

image

Notice that where it says “Suggested Values” we can select the option to use a Query as the suggested values and that’s where our previously created “Employee” Query comes in since it’ll always gives us the unique list of all the employees.

And that’s it! You’ve just created your Parameter to make things dynamic and way easier for the end-user. Let’s go with the next step.

Creating a LookUp Table for the Clever Filter

The first thing that we’re going to do is simply reference the original query.

image

On the newly created query, we’re going to be doing all of the magic that will cascade to other queries. Before we proceed, let’s go ahead and name this query LookUpProjects.

This referenced query is nothing more than the copy of the output of the Projects query. On this one, we’re going to be doing a filter on the Employee column using the value derived from the Parameter that we created. Simply select the filter option from the column and choose the one that says “equals” for the text. This will show you a new window where you can select the parameter as the input and that should look like this:

image

Now we can move to the last phase, which is simply creating the actual Clever Filter.

Performing a Clever Filter

Image result for macgyver

If you’ve had a conversation with me before about Power Query, I’ve most likely talked you into how Power Query (and Data Preparation overall) gets us to be closer to what MacGyver did on his tv show – find clever ways to solve problems with the tools that we have at hand – to be as resourceful as possible.

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.

Before we do this, let’s go ahead and do another reference on the original Projects query and rename this new query as Output.

What I’m calling a Clever Filter is nothing more than fully understanding how Merge Operations (or JOINS) work inside of Power Query and how you can use those operations to your advantage. Everybody knows how VLOOKUP can bring a value from one array to a specific cell, but what if that cell is not found, that you can just delete the current row? Well, that’s exactly what happens with the following merge operation.

image

by default we get the Left Outer join, but if we choose the Right Outer, we get all from second (LookUpProjects query) that also match from the Output query.

The result of that looks like this:

image

and that is exactly what we’re after!

I’m telling you, the average Power Query user could come up with probably as many episodes on Data Preparation as the original MacGyver show had and probably even more.

Check out the following video if you want a video demonstration of this concept