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

Filter a Column by a 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
Referencing a Column from Another Query in Power Query

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:

Power Query, Filter Column with Another Column Values from a Different Query

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])

6 thoughts on “Quick Tips: How to Filter a Column by another Column from a Different Query in Power Query

  1. Good timing. This did this a few days ago.
    1. Did a ‘Enter Data’-table: Vehicles + transformed to a List
    2. Used that list to filter a big vehicle table (by assembly-site) –> Index table
    …each List.Contains(#”Chassi tbl”, [CHNO] )
    3. Referenced that table + transformed to a List
    4. which was use to filter a very big BOM-Part table
    The two tables could now be imported /joined

    Good if
    a) we could do a Filter by List/Table in the UI, and
    b) the Native Query wouldn’t be a long IN (…500 items)

  2. Thanks for this. It could be very useful.

    To make the matching case insensitive you can add “, Comparer.OrdinalIgnoreCase” at the end of the List.Contains statement.

    You can also do partial matches by adding “, (x as text, y as text)=>Text.Contains(y,x)))”
    (See Chris Webb – https://blog.crossjoin.co.uk/2017/01/22/the-list-m-functions-and-the-equationcriteria-argument/)

    To do both partial and case insensitive matches I had to change it to “=>Text.Contains(Text.Lower(y),Text.Lower(x))”

    Is it possible to filter the second table so that only filtered matches are displayed?

  3. What’s up to all, the contents present at this web site are
    genuinely amazing for people experience, well, keep up the good work fellows.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.