#PowerQuery – Filter a table based on another table column or list – and some Filter aha’s

One of my favorite features in Excel and Power BI is Power Query / M – and I just wanted to share a small trick when you want to filter a table on specific items.

Let’s imagine you want to filter a list of customers based on different CustomerCategoryID’s –

Using the interface, you would select the different categories via the filter menu

If you select 3, 4 and 5 – you will get this filter

= Table.SelectRows(Sales_Customers, each ([CustomerCategoryID] <> 6 and [CustomerCategoryID] <> 7))

Notice that it M creates an expression that excludes 6 and 7 and not specifically selects the 3, 4 and 5 – this means that when new customer categories is created they will be included in your query as well – perhaps not what you intended!!

If you only select 3 and 4 the expression built will be

= Table.SelectRows(Sales_Customers, each ([CustomerCategoryID] = 3 or [CustomerCategoryID] = 4))

So, it seems that if you pick more than half it will build and expression with and <> statement instead of and equal statement.

To make sure that only categories that you want to include or exclude you can use a list to specify the keys to be included

To create a list you can use this expression to

= {3..5} – will give you values from 3 to 5

Or

= {3,6,5} – will give you 3, 6 and 5

To filter your table, you now need to modify the Table.SelectRows expression

= Table.SelectRows(Sales_Customers, each ([CustomerCategoryID] = 3 or [CustomerCategoryID] = 4))

To

= Table.SelectRows(Sales_Customers, each List.Contains(Query1, [CustomerCategoryID]))

The List.Contains will check whether each row in the table will have a CustomerCategoryID number that exists in the list and return true if it does and your table will then only contains rows where True is returned

If you wanted to exclude the values that you have in your list you can change the expression to

= Table.SelectRows(Sales_Customers, each List.Contains(Query1, [CustomerCategoryID]) = false

Happy Querying !

And here is an example file – Link

41 thoughts on “#PowerQuery – Filter a table based on another table column or list – and some Filter aha’s

  1. Hello Erik,
    I think you might have illustrated solution to my frustrating issue. I have three tables i joined. The first two with inner join and the last with a left outer join with a where clause. If i use just normal filter row i can get what i need but i want to be able to use another table populated by a query rather than listing the value i want. There is a query within powerbi parameter i dont quite know how to use. From your example, did you use a parameter to get your list and how did you populate your sales customer table? Thank you

    1. Hi,

      You must use a list and not a Table/Column – so you might consider converting the column to a list using the Convert to List command on the Transform tab.

      BR
      Erik

  2. Hi Erik,
    I follow the instruction and also convert the table into list, and make the command in the original table (Data) to filter the rows. But receive the error.
    I have two tables: Data table contains more rows, one column called ID showing as e,g, M3111, and second table called Query and only having list column contains similar info as the ID column.
    This is the formula I have used:
    = Table.SelectRows(Data, each List.Contains(Query, [ID]) = false)
    Then I have received this error:
    Expression.Error: A cyclic reference was encountered during evaluation.

  3. HI,
    How can I send the files to you? The list value I am using is text fomat and not consistent, like M31111, M312111 etc. Even I change the value as number, still receive same error.

  4. Hi Erik,
    I am trying to use this functionality to pass values to my BW query. So by default I have:
    {Cube.ApplyParameter, “[!V000003]”, {{“[0FISCYEAR].[K42016]”, [0FISCYEAR].[K42017]”}}},
    I created a list with Years:
    {“[0FISCYEAR].[K42016]”, [0FISCYEAR].[K42017]”}
    and I trying to pass values like you did:
    {Cube.ApplyParameter, “[!V000003]”, {each List.Contains(Years, {[List]}}},
    however I have an error:
    Expression.Error: We cannot convert a value of type Function to type List.
    Details:
    Value=Function
    Type=Type
    I was trying also List.ContainsAny, and List with brackets {[List]} and without brackets [List]
    Any ideas?
    Thanks,
    D.

      1. Hi Erik,
        yes, I have created it as a new empty Query with code:
        ={“[0FISCYEAR].[K42016]”, [0FISCYEAR].[K42017]”}
        and it is visible as a “list” in Queries list.
        when I added additional bracket before and after each
        {Cube.ApplyParameter, “[!V000003]”, {{each List.Contains(Years, {[List]}}}},
        the error is:
        Expression.Error: We cannot convert a value of type Function to type Text.
        Details:
        Value=Function
        Type=Type
        D.

  5. that is the full code for this line 🙂 the all lines (working with fixed values) looks like that:
    let
    Source = SapBusinessWarehouse.Cubes(“heiacpbc3200.hosting.heiway.net”, “51”, “300”, [ExecutionMode=SapBusinessWarehouseExecutionMode.BasXmlGzip, Implementation=”2.0″, BatchSize=10000]),
    YRBCSAM03 = Source{[Name=”YRBCSAM03″]}[Data],
    #”YRBCSAM03/YRBCSAM03_FIN_A_001″ = YRBCSAM03{[Id=”YRBCSAM03/YRBCSAM03_FIN_A_001″]}[Data],
    #”Added Items” = Cube.Transform(#”YRBCSAM03/YRBCSAM03_FIN_A_001″,
    {
    {Cube.ApplyParameter, “[!V000001]”, {{“[YCS_GROUP RE_HIER 100].[RO001OC]”, “[YCS_GROUP RE_HIER 100].[BG002OC]”}}},
    {Cube.ApplyParameter, “[!V000003]”, {{“[0FISCYEAR].[K42016]”, “[0FISCYEAR].[K42018]”, “[0FISCYEAR].[K42017]”}}},
    {Cube.ApplyParameter, “[!V000005]”, {{“[0CS_VERSION].[100]”, “[0CS_VERSION].[200]”, “[0CS_VERSION].[320]”, “[0CS_VERSION].[419]”}}},
    {Cube.ApplyParameter, “[!V000007]”, {“[0BCS_REPMOD].[S]”}},
    {Cube.ApplyParameter, “[!V000008]”, {#date(9999, 12, 12)}},
    {Cube.AddAndExpandDimensionColumn, “[YBRANDS]”, {“[YBRANDS].[LEVEL01]”}, {“Brand.Brand Level 01”}},
    {Cube.AddAndExpandDimensionColumn, “[0FISCYEAR]”, {“[0FISCYEAR].[LEVEL01]”}, {“Calendar year.Calendar year Level 01”}},
    {Cube.AddAndExpandDimensionColumn, “[YLINEEXTE]”, {“[YLINEEXTE].[LEVEL01]”}, {“Line Extension.Line Extension Level 01”}},
    {Table.AddColumn, “Line Extension.Line Extension Level 01.Key”, each Cube.AttributeMemberProperty([Line Extension.Line Extension Level 01], “[2YLINEEXTE]”)},
    {Cube.AddAndExpandDimensionColumn, “[0FISCPER3]”, {“[0FISCPER3].[LEVEL01]”}, {“Period.Period Level 01”}},
    {Cube.AddMeasureColumn, “CY Total Total Sales Volume”, “[Measures].[00733551HWKBQ9SRE1ISXQ9S200733551HWKBQ7MCXSL8ZQJUR]”},
    {Cube.AddMeasureColumn, “LY Total Total Sales Volume”, “[Measures].[00733551HWKBQ9SRE1ITMB7K200733551HWKBQ7MCXSL8ZQJUR]”},
    {Cube.AddMeasureColumn, “OG vs LY Total Sales Volume”, “[Measures].[00733551HWKBQ9SRE1IYG8HZ600733551HWKBQ7MCXSL8ZQJUR]”},
    {Cube.AddAndExpandDimensionColumn, “[YNORL]”, {“[YNORL].[LEVEL01]”}, {“No or Low Alcohol.No or Low Alcohol Level 01”}},
    {Table.AddColumn, “No or Low Alcohol.No or Low Alcohol Level 01.Key”, each Cube.AttributeMemberProperty([No or Low Alcohol.No or Low Alcohol Level 01], “[2YNORL]”)},
    {Cube.AddAndExpandDimensionColumn, “[YPRISEGM]”, {“[YPRISEGM].[LEVEL01]”}, {“Price Segmentation.Price Segmentation Level 01”}},
    {Table.AddColumn, “Price Segmentation.Price Segmentation Level 01.Key”, each Cube.AttributeMemberProperty([Price Segmentation.Price Segmentation Level 01], “[2YPRISEGM]”)},
    {Cube.AddAndExpandDimensionColumn, “[YCS_GROUP]”, {“[YCS_GROUP].[LEVEL01]”}, {“Region/OpCo.Region/OpCo Level 01”}},
    {Table.AddColumn, “Region/OpCo.Region/OpCo Level 01.Key”, each Cube.AttributeMemberProperty([#”Region/OpCo.Region/OpCo Level 01″], “[2YCS_GROUP]”)},
    {Cube.AddAndExpandDimensionColumn, “[YCS_UNIT]”, {“[YCS_UNIT].[LEVEL01]”}, {“Reporting Entity.Reporting Entity Level 01”}},
    {Table.AddColumn, “Reporting Entity.Reporting Entity Level 01.Key”, each Cube.AttributeMemberProperty([Reporting Entity.Reporting Entity Level 01], “[2YCS_UNIT]”)},
    {Cube.AddAndExpandDimensionColumn, “[YTRADPART]”, {“[YTRADPART].[LEVEL01]”}, {“Trading Partner.Trading Partner Level 01”}},
    {Cube.AddAndExpandDimensionColumn, “[0CS_VERSION]”, {“[0CS_VERSION].[LEVEL01]”}, {“Version.Version Level 01”}},
    {Table.AddColumn, “Version.Version Level 01.Key”, each Cube.AttributeMemberProperty([Version.Version Level 01], “[20CS_VERSION]”)},
    {Cube.AddAndExpandDimensionColumn, “[YPRDTYP]”, {“[YPRDTYP].[LEVEL01]”}, {“Product Type.Product Type Level 01”}},
    {Table.AddColumn, “Product Type.Product Type Level 01.Key”, each Cube.AttributeMemberProperty([Product Type.Product Type Level 01], “[2YPRDTYP]”)},
    {Table.AddColumn, “Trading Partner.Trading Partner Level 01.Key”, each Cube.AttributeMemberProperty([Trading Partner.Trading Partner Level 01], “[2YTRADPART]”)}
    }),

  6. Hi

    It seems like you are trying to parametrize your query to the Server – and if that is so you can modify the step to the following

    Create a Parameter with the following – call it YearFilter

    let
    Source = {{“[0FISCYEAR].[K42016]”, “[0FISCYEAR].[K42017]”}}
    in
    Source

    Modify your
    {Cube.ApplyParameter, “[!V000003]”, {{“[0FISCYEAR].[K42016]”, “[0FISCYEAR].[K42018]”, “[0FISCYEAR].[K42017]”}}},

    to
    {Cube.ApplyParameter, “[!V000003]”, YearFilter},

    It seems like you have forgotten an extra {} in your list – it needs to be a list within a list.

    Hope that is solves your issue

    /Erik

  7. I am running into the below error, any advise?
    Expression.Error: We cannot convert a value of type Table to type List.
    Details:
    Value=Table
    Type=Type

  8. Erik,

    Any thoughts on the use of filter with this method versus using a Full Inner Join between the columns in question? I initially came from an SQL background and using Inner Joins as a dynamic filter mechanism makes sense to me, but I’m now more interested in performance aspects, something not covered in the Power BI Certification exam.

    We are primarily using M to import selected data from an excel Sharepoint warehouse into individual data files.

    Thanks,
    Julius

    1. Hi Julius – sorry for the late answer. Performance will suffer if you are using this on large tables – the simulated inner join by using this method will not perform well on large excel files and especially if they are located on sharepoint – then the sharepoint connector also has some performance issues – performance can be hard to measure and debug in M – Erik

  9. Hello, Erik,

    how can I do it that a filter is used if it contains elements, but ignored if it contains no elements?
    It would be great if you had an answer to this question

    Thanks,
    Wilfried

      1. Hi,
        I’d like to do pretty much the same thing but with text in Excel.
        Let me try and explain with a cut down version.

        I have a column (Component Description ) in a table, Table1,

        Table1
        Component Description Dept.
        Cartridge – Doughnut POWER
        Manifold – Collector Jig POWER
        Manifold – NRV’s MECH

        List of words to filter on In a different sheet of the same workbook. I actually want to exclude any component Description with the

        Exclude List
        shotpeen
        Jig

        So Table1 Would end up being:

        Component Description Dept.
        Cartridge – Doughnut POWER
        Manifold – NRV’s MECH

        Obviously the list is much longer but would dynamically filtered if you added to the List.

        Many thanks in advance.

  10. Hi

    I wish to filter Lease end date column by today’s date which is available in table in query editor

    Lease End Date -Today’s Date
    7/29/2018 10/14/2019
    7/29/2018 10/14/2019
    7/29/2018 10/14/2019
    7/29/2021 10/14/2019
    7/29/2018 10/14/2019
    7/29/2018 10/14/2019
    7/29/2019 10/14/2019
    7/29/2019 10/14/2019
    7/29/2018 10/14/2019
    7/29/2021 10/14/2019
    7/29/2020 10/14/2019
    7/29/2020 10/14/2019
    2/28/2019 10/14/2019
    2/28/2019 10/14/2019
    2/28/2019 10/14/2019

    Please help

  11. Hi Erik!
    amazing! very useful trick!
    my question is how could I do the opposite? I mean select the rows with values not contained in the list?
    since there is not a List.NotContains() command, how could you achieve this?

    regards

  12. This is such a great solution, thank you for sharing it Erik!

    Could you also add an example of how to do this with Table.Contains if I have multiple columns that I want to filter over? The only way I’ve figured out is using this approach and having functions concat the fields together.

      1. Hi Aaron,

        Here is one way of doing it with Power Query

        let
        Source = Original,
        #”Added Custom” = Table.AddColumn(Source, “Custom”, each List.PositionOf(Group[Key], [Key])),
        #”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Custom.1″, each Group[Step]{[Custom]}),
        #”Added Custom2″ = Table.AddColumn(#”Added Custom1”, “Custom.2″, each Group[Key]{[Custom]}),
        #”Added Custom3″ = Table.AddColumn(#”Added Custom2”, “Custom.3″, each [Key] = [Custom.2] and [Step] = [Custom.1]),
        #”Filtered Rows” = Table.SelectRows(#”Added Custom3″, each ([Custom.3] = true))
        in
        #”Filtered Rows”

        You should wrap it in a function and the performance on a large dataset will probably not be impressive.

        Hope this helps you

        Best
        Erik

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s