Posted on in Uncategorized

#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

66 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

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

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

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

  9. 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]”)}
    }),

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

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

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

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

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

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

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

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

  21. Hi Adrian/Erik
    Been reasing your posts and hoping you could possibly help.
    My objective is for PQ to remove (filter-out) rows based on criteria from a second table.

    ie
    Table1 is a simple one-column table with rows containing values 2,3,4,5,6,7,8,9

    Table2 is also a simple one column table with rows containing 3,5,7

    I wish to remove rows from table 1
    where
    table1.val1 max(table2.val)

    I’ve been exploring the article below but can’t seem to get correct syntax
    https://www.powerquery.io/table/table.selectrows

    Any advice would be very much apreciated

    Thanks

    Steve

    1. Hi Stephen

      This might be the solution

      // Table1
      let
      Source = {2,3,4,5,6,7,8,9},
      #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #”Filtered Rows” = Table.SelectRows(#”Converted to Table”, each ([Column1] = List.Max(Table2[Column1])))
      in
      #”Filtered Rows”

      // Table2
      let
      Source = {3,5,7},
      #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
      in
      #”Converted to Table”

      Let me know if it isnt 🙂

      Best
      Erik

  24. hi Erik, I’d like to create new tables by filtering each column value. Please may you give me some help ?
    thanks
    John

  25. Dear Erik,
    Thank you very much for your solution.
    I tried but I also have an error message query. Expression.Error: A cyclic reference was encountered during evaluation.
    My data source is folder.
    I have a table with product numbers, import country, export country (in the form of country codes US,MX,CN…, etc), and so on.
    Then, I created an extra list in power bi query, containing some country codes. I would like to filter the export country in the table based on the list.
    If you have time, could you please help me with this issue?
    my code is here:
    Source = Folder.Files(“\\fe01fs15.de.bosch.com\GS-LOF$\Internal\10_Departments\10_LOF1\30_Processes\302_Global_Processes\3037_Master_SIBE\Project_Document_Vicky\FTA_BOOM_UP\5_DE\testing”),
    #”Filtered Hidden Files1″ = Table.SelectRows(Source, each [Attributes]?[Hidden]? true),
    #”Invoke Custom Function1″ = Table.AddColumn(#”Filtered Hidden Files1″, “Transform File”, each #”Transform File”([Content])),
    #”Renamed Columns1″ = Table.RenameColumns(#”Invoke Custom Function1″, {“Name”, “Source.Name”}),
    #”Removed Other Columns1″ = Table.SelectColumns(#”Renamed Columns1″, {“Source.Name”, “Transform File”}),
    #”Expanded Table Column1″ = Table.ExpandTableColumn(#”Removed Other Columns1″, “Transform File”, Table.ColumnNames(#”Transform File”(#”Sample File”))),
    #”Changed Type” = Table.TransformColumnTypes(#”Expanded Table Column1″,{{“Source.Name”, type text}, {“ExportCountry”, type text}, {“SupplierName”, type text}, {“ImportCountry”, type text}, {“HsNum”, Int64.Type}, {“ProductNum”, type text}, {“ProductDesc”, type text}, {“BusinessUnit”, type text}, {“BusinessDivision”, type text}, {“CountryOfOrigin”, type text}, {“EstBaseTotalDutiableLineValue”, type number}, {“AdValoremDutyRate”, type number}, {“EstBaseTotalLineDuty”, type number}, {“InvoiceNum”, type text}, {“ImportDate”, type datetime}}),
    #”Removed Columns” = Table.RemoveColumns(#”Changed Type”,{“Source.Name”}),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Removed Columns”,{{“HsNum”, type text}}),
    #”Split Column by Delimiter” = Table.SplitColumn(Table.TransformColumnTypes(#”Changed Type1″, {{“ImportDate”, type text}}, “en-US”), “ImportDate”, Splitter.SplitTextByDelimiter(“/”, QuoteStyle.Csv), {“ImportDate.1”, “ImportDate.2”, “ImportDate.3″}),
    #”Changed Type2″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“ImportDate.1”, Int64.Type}, {“ImportDate.2”, Int64.Type}, {“ImportDate.3″, type text}}),
    #”Removed Columns1″ = Table.RemoveColumns(#”Changed Type2”,{“ImportDate.2″}),
    #”Split Column by Delimiter1″ = Table.SplitColumn(#”Removed Columns1”, “ImportDate.3″, Splitter.SplitTextByEachDelimiter({” “}, QuoteStyle.Csv, false), {“ImportDate.3.1”, “ImportDate.3.2″}),
    #”Changed Type3″ = Table.TransformColumnTypes(#”Split Column by Delimiter1”,{{“ImportDate.3.1”, Int64.Type}, {“ImportDate.3.2″, type time}}),
    #”Removed Columns2″ = Table.RemoveColumns(#”Changed Type3”,{“ImportDate.3.2″}),
    #”Renamed Columns” = Table.RenameColumns(#”Removed Columns2″,{{“ImportDate.1”, “Import Month”}, {“ImportDate.3.1”, “Import Year”}}),
    #”Inserted First Characters” = Table.AddColumn(#”Renamed Columns”, “First Characters”, each Text.Start([ProductNum], 10), type text),
    #”Renamed Columns2″ = Table.RenameColumns(#”Inserted First Characters”,{{“First Characters”, “PN-10 Digit”}}),
    #”Reordered Columns” = Table.ReorderColumns(#”Renamed Columns2″,{“ExportCountry”, “SupplierName”, “ImportCountry”, “HsNum”, “PN-10 Digit”, “ProductNum”, “ProductDesc”, “BusinessUnit”, “BusinessDivision”, “CountryOfOrigin”, “EstBaseTotalDutiableLineValue”, “AdValoremDutyRate”, “EstBaseTotalLineDuty”, “InvoiceNum”, “Import Month”, “Import Year”}),
    #”Removed Columns3″ = Table.RemoveColumns(#”Reordered Columns”,{“ProductNum”}),
    #”Reordered Columns1″ = Table.ReorderColumns(#”Removed Columns3″,{“ImportCountry”, “HsNum”, “PN-10 Digit”, “ExportCountry”, “CountryOfOrigin”, “SupplierName”, “ProductDesc”, “BusinessUnit”, “BusinessDivision”, “EstBaseTotalDutiableLineValue”, “AdValoremDutyRate”, “EstBaseTotalLineDuty”, “InvoiceNum”, “Import Month”, “Import Year”}),
    #”Renamed Columns3″ = Table.RenameColumns(#”Reordered Columns1″,{{“CountryOfOrigin”, “Country Of Origin”}})
    in
    #”Renamed Columns3″= Table.SelectRows(testing, each List.Contains(FTA, [Country Of Origin]))

    1. Hi Vicky,

      I think the problem is located in the last 3 rows

      #”Renamed Columns3″ = Table.RenameColumns(#”Reordered Columns1″,{{“CountryOfOrigin”, “Country Of Origin”}})
      in
      #”Renamed Columns3″= Table.SelectRows(testing, each List.Contains(FTA, [Country Of Origin]))

      Is the FTA list in any way related to your query ?

      If not try and modify the lines to this

      #”Renamed Columns3″ = Table.RenameColumns(#”Reordered Columns1″,{{“CountryOfOrigin”, “Country Of Origin”}}),
      xx = Table.SelectRows(testing, each List.Contains(FTA, [Country Of Origin]))
      in
      xx

      You should also consider cleaning up the number of steps as you have RemoveColumns, ReOrderColumns and RenameColumns several times.

      It will improve your query 🙂

      BR
      Erik

      1. Hello Erik,
        Thank you very much for the quick reply! I really appreciate your help.
        1. I would like to ask you how I can clean up the number of steps if I really need to rename and removed the columns? I don’t prefer to do it in excel before upload to power bi, because otherwise I need to modify every excel files in the folder (the format of the files is standard in my system)

        2. I am not sure what you mean if FTA is anyway related to my query and what is the effect of that. The FTA list is created separately by me. The list contains all the country codes where there is a FTA. The range is not the same as the column”CountryOfOrigin” in the table. It means that the list contains some country codes that are not in the table. Also, in the column”CountryOfOrigin” in the table, there are some country codes which are not in the list.

        3. Based on this list, I would like to filter the column “CountryOfOrigin”, which refers to “Columns 3″ in the code(I renamed the column only because I don’t know which name it is in the advanced editor). So I deleted the step #”Renamed Columns3″ = Table.RenameColumns(#”Reordered Columns1″,{{“CountryOfOrigin”, “Country Of Origin”}}) and changed it to #”Columns3″=Table.SelectRows(testing, each List.Contains(FTA, [Country Of Origin]))
        in #”Columns3”
        But I still got the same error message Expression.Error: A cyclic reference was encountered during evaluation.

      2. Hi Vicky,

        Ad 1 – You have several step that removes columns and rename columns – if you do that at the end of your query you will only need to do this in one step for removal and one for renaming.

        And for instance these two lines can be reduced to one

        #”Inserted First Characters” = Table.AddColumn(#”Renamed Columns”, “First Characters”, each Text.Start([ProductNum], 10), type text),
        #”Renamed Columns2″ = Table.RenameColumns(#”Inserted First Characters”,{{“First Characters”, “PN-10 Digit”}}),

        instead

        #”Inserted First Characters” = Table.AddColumn(#”Renamed Columns”, “PN-10 Digit”, each Text.Start([ProductNum], 10), type text),

        Ad 2 – If the FTA is created by using “Enter data” it shouldnt be a problem – You can check via Query Dependencies via the view tab in the query editor.

        If you can share a demo data file and your queries I can take a look if you want – es @ catmansolution.com

        BR
        Erik

      1. Hi Erik,

        I have a similar problem than Peter.
        I have a Table1 imported from excel. I want to filter out cases where “RecordDate” is older or equal than a date that its calculated from a parameter entered manually.

        I am trying to create a list that uses the date parameter to calculate the new date (this I managed to do) but I cannot filter Table1 by that calculated date in the list.

        I hope you can help me

  27. Hello Erik – how would you change this formula if you wanted to look for the current row’s value in another column?

    I have a table that contains columns for employee ID and supervisor ID. I want to create a column that looks if the current row’s employee ID is present anywhere in the supervisor ID column.

    1. Hi Colin,

      Here is an example on how you can do it

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlTSUQrJSFVwyi8uBjKVYnWilYyADNeizGwgZQgWMAayAlJLUovgIiZAlnN+TmYekDZSio0FAA==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, Name = _t, SuperVisor = _t]),
      TheTable = Table.TransformColumnTypes(Source,{{“EmployeeID”, Int64.Type}, {“Name”, type text}, {“SuperVisor”, Int64.Type}}),
      #”Added Custom” = Table.AddColumn(TheTable, “SuperVisorName”, each try TheTable{List.PositionOf(TheTable[EmployeeID], [SuperVisor])}[Name] otherwise “No Boss”)
      in
      #”Added Custom”

      If its a large table you properly should use the merge tables instead.

      And an example using merge

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlTSUQrJSFVwyi8uBjKVYnWilYyADNeizGwgZQgWMAayAlJLUovgIiZAlnN+TmYekDZSio0FAA==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, Name = _t, SuperVisor = _t]),
      TheTable = Table.TransformColumnTypes(Source,{{“EmployeeID”, Int64.Type}, {“Name”, type text}, {“SuperVisor”, Int64.Type}}),
      #”Merged Queries” = Table.NestedJoin(TheTable, {“SuperVisor”}, TheTable, {“EmployeeID”}, “TheTable”, JoinKind.LeftOuter),
      #”Expanded TheTable” = Table.ExpandTableColumn(#”Merged Queries”, “TheTable”, {“Name”}, {“TheTable.Name”})
      in
      #”Expanded TheTable”

      Hope this helps you

      /Erik

  29. Does this only work with relatively small files. I have a 50,000 line table that refreshes fine without a filter, but as soon as I apply a List.Contains step against another list to filter a text based column the spreadsheet just hangs ..

    1. It will not perform well on large datasets – but the source of table will also influence the performance – if its excel file located in sharepoint it will perform bad but you might see a better performance if your source is SQL Table

  31. That’s a great post.
    I want to do the same but with dates.
    For Example I have two tables Sales and Order. I want to filter the sales table by Sales Date in such a way that the Sales Date >= Max Order Date.
    I have created a list for max order date, and providing this to List.Contains is filtering sales table to have only one Sales date which is equal to max of order date, and i want sales date is on or after the max of order date.
    I would really appreciate any help. thank you.

    1. Hi I am on holiday without access to a computer – but Without intellisense I would think that the formula would look something like this =Table.SelectRows(#”order table”, each [salesdate] >= List.Max(Order[orderdate]) – depnding on your datasource and amount of data you might run into performance issues – so think about query folding Best Erik

Leave a comment