Business Intelligence - BI, Excel, Power BI, Power BI Desktop, Power Query, Quick Tips, Self-Service BI, T-SQL

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

Published by Soheil Bakhshi

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

    Reply

  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?

    Reply

  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.

    Reply

  4. I have a situation where I want to filter my table to only contain entries that occurred on a Monday (which means I would filter on a column from my date table). So I’m not filtering by all the values in the date table, but only some of them. How would I do that?

    Reply

    1. Hi MJ,

      Welcome to BIInsight.com.
      It sounds like you don’t want permanently filter a table by values of a column in your Date dimension, right?
      If that’s the case then you probability don’t want to handle the filtering in Power Query layer (in Query Editor), but in DAX.

      Please note that Power Query is the Data preparation layer, therefore the result of filtering are permanently loaded into your model. So if you want to take the filter out then you always have to manage it from Query Editor.

      If you elaborate your scenario a bit more then I can provide better guidance.

      Thanks

      Reply

  5. Hello

    Thank you for your Post, very helpful!

    In my case i want the exact opposite of your case, i want to remove the values that are in the list instead of keeping them.

    So is there a way of doing the opposite of Table.SelectRows?

    Reply

    1. Hi Markus,

      Welcome to BIInsight.com.
      Well, to contain values that are NOT in the list you simply need to add a “not” before the “List.Contains” function.
      So the code would be like this:


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

      Cheers.

      Reply

  6. Tnx for the Post- Really helpful but does not quite work for me.
    I have two tables with NO columns related (Join not possible)
    One column in Table A is named PARTNO and has lots of Part number separated by comma in each ROW e.g

    PARTNO
    802300-14,802300-14,NONPROCI,803753-02,
    D2727000700200,D2727000700600,A3410-2,

    Table B (named CMRoWIP) has one Item No in each row of Column named “Item No” e.g

    Item No
    PPC1100-00
    A3410-2

    Based on your post I used following in query of Table A
    #”Filtered Rows” = Table.SelectRows(#”Promoted Headers”, each List.Contains(#”CMRoWIP”[Item No], [PARTNO]))
    in
    #”Filtered Rows”

    Query is accepted without errors but the result is blank rows.
    Moreover it shows about 2GB of data reviewed whereas the file for table CMROwip is less than 500 KB.
    What I want to do is filter Table A in a way where it only shows the rows where PartNO columns has “*Item no*”
    Using Excel but if it can be done in MS Flow, I am ok with it
    What am I doing wrong?
    Please assist

    Reply

    1. Hi Navaid,

      Welcome to BIInsight.com.
      Look, what you need is to split the comma separated text to a list of values with Text.Split function, then use List.ContainsAny function as opposed to List.Contains.
      The List.ContainsAny function looks for any values of a list matching the values of another list which is exactly what you’re after.
      The code below should work then:

      #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each List.ContainsAny(#"CMRoWIP"[Item No], Text.Split([PARTNO], ",")))
      in
      #"Filtered Rows"

      Please let me know how it works.
      Cheers.

      Reply

  7. Hi,
    I need to filter my list in the first table to include values greater than the max value in the same column in a second table/query
    So I am using
    let
    Source = Excel.CurrentWorkbook(){[Name=”MainEstateList”]}[Content],
    #”Changed Type” = Table.TransformColumnTypes(Source,{{“Code”, Int64.Type}, {“Name”, type text}}),
    #”Filtered Rows1″ = Table.SelectRows(#”Changed Type”, each ([Name] null)),
    #”MaxMain” = Table.SelectRows(#”Filtered Rows1″, each List.Max(#”MainList_Page1″[Code], [Code]))
    in
    #”MaxMain”

    But on the last step I get the below, both source and filter columns are set to type Int64 so can’t see where I am going wrong.

    Expression.Error: We cannot convert the value 67 to type Logical.
    Details: Value=67 Type=[Type]

    Reply

      1. Hi Aaron,

        I am interested in your approach, but I don’t want to keep the original table. Or, rather, filter down the original table. Consider I have a table of 1 million users, but want to import those from a specific set of cohorts. Those cohorts are managed via another table and joined appropriately with the user table.

        The idea being to keep the model size down and only import the needed users. Can’t do this in SQL for my present use case.

        Thoughts?

        Reply

  9. Thanks for sharing. I learned a new trick here.

    I’m just wondering if there’s any difference if we use a merge query to merge two tables based on the column we’d like to filter? This is how I filter my table before I saw this post.

    Merge query works fine with my data where both the tables contains thousands of rows, and it can be done in about 30 secs at most. On the other hand, the List.Contains trick also works, but it takes about 8-10 mins to finish the query.

    Not sure if I’m doing anything wrong but it’d be useful if a comparison on the constraints of both approaches and the performance difference between them could be addressed.

    Again, thanks for sharing. I learned a new trick and enjoyed it.

    Reply

  10. Thanks for this helpful post! however for some reason it doesn’t work for me. I have 2 queries, in the 1st query I have say region names and region codes columns in the second query I region codes column only, so i’m trying to replace the region codes in the second query with the region names from 2st query. I followed your instructions, below is line of code, it doesn’t through any errors but doesn’t do anything. I’m not sure what am I doing wrong here. I was wondering if you can suggest a solution here. Thanks!!
    = Table.ReplaceValue(#”Removed Other Columns”,TestQuery1[Region],TestQuery1[Region Name],Replacer.ReplaceValue,{“Region”})

    Reply

    1. Hi Diana,

      Welcome to BIInsight.com.
      This post is about filtering rows by values from a column different table.
      Looking at your code you are missing each before the referenced column and the referencing column. Your code structure should look like below:

      =Table.ReplaceValue(Source, each [A], each [C],Replacer.ReplaceText,{"B"})

      I suggest you have a look at this post that might help you achieve what you are after.

      Please let us know how you get on with your scenario.
      Cheers.

      Reply

  11. Very happy to find this simple solution because, as one who’s familiar with SQL, I dislike using “Merge Query” for the sole purpose of filtering records.

    Reply

Leave a Reply

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


The reCAPTCHA verification period has expired. Please reload the page.