Quick Tips: How to Filter a Column by another Column from a Different Query in Power Query

A while ago I was visiting a customer that asked if they can filter a query data by a column from another query in Power BI. And I said of course you can. In this post I explain how that can be achieved in Power Query. The key point is to know how to reference a query and how to reference a column of that query in Power Query. This is useful when you have a lookup table that can be sourced from every supported data source in Power Query and you want to filter the results of another query by relevant column in the lookup query. In that case, you’ll have a sort of dynamic filtering. So, whenever you refresh your model if new records have been changed in or added to the source of the lookup query, your table will automatically include the new values in the filter step in Power Query.

Referencing a Query

It is quite simple, you just need to use the name of the query. If the query name contains special characters like space, then you need to wrap it with number sign and double quotes like #”QUERY_NAME”. So, if I want to reference another query, in a new blank query, then the Power Query (M) scripts would look like below:

let
    Source = Product
in
    Source

Or something like

let
    Source = #"Product Category"
in
    Source

Referencing a Column

Referencing a column is also quite simple. When you reference a column you need to mention the referencing query name, explained above, along with the column name in brackets. So, the format will look like #”QUERY_NAME”[COLUMN_NAME]. The result is a list of values of that particular column.

let
    Source = #"Product Category"[Product Category Name]
in
    Source

Filtering a Query Column with Referencing Column from Another Query

Filtering a column using the query editor UI is fairly simple. You just need to select the needed values from dropdown and it’s done. But the query in that case is filtered with constant values. So if your reporting requirement changes in the future, you’ll need to redo the filtering and refresh the query. Our scenario is a bit different though, we want to filter a column by values from another column. I just mentioned earlier how easily you can reference a column from another table. I also mentioned that the results of that referencing would be a List of values right? So what we are after is filtering a column by a list of values. There is a function in Power Query that makes it easy, List.Contains(list, values).

I’d rather explain the rest with a scenario. I have a Product Subcategory table containing descriptive data of all product subcategories. The business now has a reporting requirement that I have to filter the Product Subcategory names by data from another table. The second table contains only approved subcategories. The second table name is “Product Subcategory Lookup”. The data in the “Product Subcategory Lookup” is frequently updated by the business.

The only thing I need to do is to do is to use the List.Contains function like below:

List.Contains(#"Product Subcategory Lookup"[Approved Subcategory], [Subcategory Name])

If you’re used to use the query editor UI then you can easily apply a filter to the [Subcategory Name], then change the code as below:

If you’re more hands-on and prefer writing the M codes then use the Advanced Editor to type the codes.

#"Filtered Rows" = Table.SelectRows(#"PREVIOUS_STEP", each List.Contains(#"REFERENCED_TABLE"[REFERENCED_COLUMN], [COLUMN_TO_BE_FILTERED]))

For those of you who are more familiar with SQL, the above M code works similar to the below SQL script (if your source is SQL Server):

SELECT productsubcategorykey          
       , productsubcategoryalternatekey 
       , [Subcategory Name]
FROM   DimProductSubcategory
WHERE  [Subcategory Name] IN (SELECT [Approved Subcategory] 
							  FROM [Product Subcategory Lookup])