Results 1 to 7 of 7

Thread: Table.RemoveMatchingRows issue

  1. #1
    Acolyte El Cid's Avatar
    Join Date
    Aug 2016
    Location
    Greenville, SC
    Posts
    50
    Articles
    0
    Excel Version
    Excel 2016

    Table.RemoveMatchingRows issue



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to use a table named tblSection267 with a list of names under the column heading DELETEnames to remove names listed rows in the column named "Source Name" in the another table (named #"Append Payrolls") and I can't seem the get the syntax right. Here's the my code I've tried. I've commented out the offending line of code and left the one that works below it, but would prefer to pull the names from the tblSection267 table that is a separate query:

    let
    Source = tblPayroll01,
    #"Append Payrolls" = try Table.Combine({tblPayroll01, tblPayroll02}) otherwise Source,
    // #"Filtered Rows" = Table.RemoveMatchingRows(#"Append Payrolls", Table.ToRows (tblSection267 [DELETEnames]),[Source Name]),
    // #"Filtered Rows" = Table.RemoveMatchingRows(#"Append Payrolls", Table.ToList(tblSection267[DELETEnames], Combiner.CombineTextByDelimiter(",")),[Source Name]),
    #"Filtered Rows" = Table.SelectRows(#"Append Payrolls", each ([Source Name] <> "BUGGS BUNNY" and [Source Name] <> "DONALD DUCK" and [Source Name] <> "MICKY MOUSE")),

  2. #2
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    103
    Articles
    0
    Excel Version
    Excel 365
    The are many possible ways to do that.
    One of them below
    Code:
    #"Filtered Rows" = Table.SelectRows(#"Append Payrolls", each List.Contains(tblSection267[DELETEnames],  [Source Name]) )
    Regards

  3. #3
    Acolyte El Cid's Avatar
    Join Date
    Aug 2016
    Location
    Greenville, SC
    Posts
    50
    Articles
    0
    Excel Version
    Excel 2016
    Thanks Bill. That didn't work and it ran for an incredibly long time, like it was in a circular loop and finally escaped and shrunk the resulting table down a few lines. Maybe I wasn't clear enough...I just want to reference a table in the Table.SelectRows function where the column named "Source Name" in the "Append Payrolls" table do not contain any of the names found on the table named tblSection267. Si I know this works:
    #"Filtered Rows" = Table.SelectRows(#"Append Payrolls", each ([Source Name] <> "BUGGS BUNNY" and [Source Name] <> "DONALD DUCK" and [Source Name] <> "MICKY MOUSE")),

    but I want to take this section..."
    each ([Source Name] <> "BUGGS BUNNY" and [Source Name] <> "DONALD DUCK" and [Source Name] <> "MICKY MOUSE")"

    and make it point at a dynamic table that is named tblSection267 and has the header named DELETEnames.




  4. #4
    Acolyte El Cid's Avatar
    Join Date
    Aug 2016
    Location
    Greenville, SC
    Posts
    50
    Articles
    0
    Excel Version
    Excel 2016
    This seems to make it work. Thanks for the help.

    #"Filtered Rows" = Table.SelectRows(#"Append Payrolls", each not List.Contains(tblSection267[DELETEnames], [Source Name])),

    Also found I could have used an anti-join, but I didn't care to use that approach.
    Last edited by El Cid; 2019-03-06 at 06:13 PM. Reason: Make additional observation

  5. #5
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    103
    Articles
    0
    Excel Version
    Excel 365
    I did not understand your needs correctly. Maybe this time... :-))
    After #"Append Payrolls" step:
    Code:
    BufferedList = List.Buffer(tblSection267[DELETEnames]),
    #"Filtered Rows" = Table.SelectRows(#"Append Payrolls", each not List.Contains(BufferedList,  [Source Name]) )
    BufferedList should speed up your query.

  6. #6
    Acolyte El Cid's Avatar
    Join Date
    Aug 2016
    Location
    Greenville, SC
    Posts
    50
    Articles
    0
    Excel Version
    Excel 2016
    Thanks Bill, I'll give it a try.

  7. #7
    Acolyte El Cid's Avatar
    Join Date
    Aug 2016
    Location
    Greenville, SC
    Posts
    50
    Articles
    0
    Excel Version
    Excel 2016
    Thanks Bill! That piece of code made a huge difference.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •