none
How do I removing a duplicate from Nested Table? RRS feed

  • Question

  • Hi everyone... I am trying to filter a nested table BEFORE expanding it by removing duplicate Location IDs.  I want to filter the Location ID filed in the Nested Table to NOT =  the Location ID in the current record.  Is this possible?

    Each nested table has at least one duplicate record.  (I know I could expand the table and add a custom column to check for Location ID <> Location ID.1, but I was hoping to pre-filter the nested table before expanding it to hopefully improve performance.)

    I tried adding a column like this:

    = Table.AddColumn(AddField_AllStoreAndDist, "FilteredLocationID", each Table.SelectRows([AllStoreAndDist], [AllStoreAndDist][Location ID] <> [Location ID] ))

    But that gives me an error: Can't convert the value true into type Function.

    Thanks...


    John Thomas

    Wednesday, June 3, 2020 9:17 PM

Answers

  • Hi John

    One option

    Filtered = Table.AddColumn(AddField_AllStoreAndDist, "FilteredLocationID", each
        Table.SelectRows([AllStoreAndDist], (inner)=> inner[Location ID] <> [Location ID]),
        type table
    )

    or

    Filtered = Table.AddColumn(AddField_AllStoreAndDist, "FilteredLocationID", (outer)=>
        Table.SelectRows(outer[AllStoreAndDist], each [Location ID] <> outer[Location ID]),
        type table
    )

    or

    Filtered = Table.AddColumn(AddField_AllStoreAndDist, "FilteredLocationID", (outer)=>
        Table.SelectRows(outer[AllStoreAndDist], (inner)=> inner[Location ID] <> outer[Location ID]),
        type table
    )

    BTW, I was quite sure this question was already asked > This thread is very close to yours


    • Edited by Lz._ Thursday, June 4, 2020 9:18 AM Add. info
    • Marked as answer by jbt_PwrPvt Thursday, June 4, 2020 7:16 PM
    Thursday, June 4, 2020 4:57 AM

All replies

  • Hi John

    One option

    Filtered = Table.AddColumn(AddField_AllStoreAndDist, "FilteredLocationID", each
        Table.SelectRows([AllStoreAndDist], (inner)=> inner[Location ID] <> [Location ID]),
        type table
    )

    or

    Filtered = Table.AddColumn(AddField_AllStoreAndDist, "FilteredLocationID", (outer)=>
        Table.SelectRows(outer[AllStoreAndDist], each [Location ID] <> outer[Location ID]),
        type table
    )

    or

    Filtered = Table.AddColumn(AddField_AllStoreAndDist, "FilteredLocationID", (outer)=>
        Table.SelectRows(outer[AllStoreAndDist], (inner)=> inner[Location ID] <> outer[Location ID]),
        type table
    )

    BTW, I was quite sure this question was already asked > This thread is very close to yours


    • Edited by Lz._ Thursday, June 4, 2020 9:18 AM Add. info
    • Marked as answer by jbt_PwrPvt Thursday, June 4, 2020 7:16 PM
    Thursday, June 4, 2020 4:57 AM
  • Lz_,

    Just what I needed!  

    Do you happen to know any good books that cover nested table manipulations such as this?  It would really come in handy...

    Thanks.


    John Thomas


    • Edited by jbt_PwrPvt Thursday, June 4, 2020 7:19 PM
    Thursday, June 4, 2020 7:17 PM
  • Hi John

    I can't recommend any as I haven't read one yet :-((

    M is for (DATA) Monkey by Ken Puls & Miguel Escobar + Collect, Combine and Transform Data Using Power Query in Excel and Power BI by Gil Raviv are well known one but no idea how deep they go into such topic though

    BTW John,
    Although this doesn't relate to your question but as you talk about Expanding, if [AllStoreAndDist] results of a join/merge operation you might be interested in reading Chris Webb:

    • Edited by Lz._ Friday, June 5, 2020 8:26 AM Add. info
    Friday, June 5, 2020 4:10 AM