Clever Filters in Power BI / Power Query with Merge Operations

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:

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

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”.

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”:

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”.

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

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.

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:

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

Performing a Clever Filter

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.

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:

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