Forum Discussion

Mikee5's avatar
Mikee5
Regular Visitor
3 years ago
Solved

Delete Rows based on conditions

I am trying to clean rows containing Credit Notes as well as the rows with matching Invoices. For example I want to delete the row with CN-1234 as well as the row INV-1234.  It is easy to delete all the CN rows, but how do I delete the matching INV row as well? Just a newbie so any advice welcome.

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hi, Mikee5 one more

    let
    Source = YourSource,
    cr_notes = List.Buffer(List.Select(Source[YourColumn], (x) => Text.Contains(x, "CN"))),
    lst = List.Buffer(List.Combine({List.Transform(CN, (x) => Text.Replace(x, "CN", "INV")), cr_notes})),
    select = Table.SelectRows(Source, (x) => not List.ContainsAny(x[YourColumn], lst))
in 
    select

     

6 Replies

  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi

     

    Select CN, replace "CN" by "INV" and join Source with CN+INV, JoinKind = LeftAnti

     

    let
    Source = YourSource,
    CN = Table.SelectRows(Source, each Text.Contains([YourColumn], "CN")),
    INV = Table.ReplaceValue(CN,"CN","INV", Replacer.ReplaceText,{"YourColumn"}),
    Join_LeftAnti = Table.NestedJoin(Source, {"YourColumn"}, Table.Combine({INV, CN}), {"YourColumn"}, "NewColumn", JoinKind.LeftAnti)
    in
    Join_LeftAnti

     

    Stéphane

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Mikee5 ,

     

    Provide another idea:

    let
    Source = YourDataSource,
    AddCustomColumn = Table.AddColumn(Source, "InvoiceNumber", each Text.AfterDelimiter([ID], "-")),
    GroupRows = Table.Group(AddCustomColumn, {"InvoiceNumber"}, {{"Count", each Table.RowCount(_), type number}, {"Data", each _, type table}}),
    FilterGroups = Table.SelectRows(GroupRows, each [Count] = 1),
    ExpandGroups = Table.ExpandTableColumn(FilterGroups, "Data", Table.ColumnNames(Source)),
    RemoveColumns = Table.RemoveColumns(ExpandGroups, {"InvoiceNumber", "Count"})
in
    RemoveColumns

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hi, Mikee5 one more

    let
    Source = YourSource,
    cr_notes = List.Buffer(List.Select(Source[YourColumn], (x) => Text.Contains(x, "CN"))),
    lst = List.Buffer(List.Combine({List.Transform(CN, (x) => Text.Replace(x, "CN", "INV")), cr_notes})),
    select = Table.SelectRows(Source, (x) => not List.ContainsAny(x[YourColumn], lst))
in 
    select

     

    • slorin's avatar
      slorin
      Super User
      3 years ago

      Hi, AlienSx 

       

      let
          Source = YourSource,
          cr_notes = List.Buffer(List.Select(Source[YourColumn], (x) => Text.Contains(x, "CN"))),
          lst = List.Buffer(List.Combine({List.Transform(cr_notes, (x) => Text.Replace(x, "CN", "INV")), cr_notes})),
          select = Table.SelectRows(Source, (x) => not List.ContainsAny({x[YourColumn]}, lst))
      in 
          select

       Stéphane

      • AlienSx's avatar
        AlienSx
        Super User
        3 years ago

        slorin thank you, mate! Better choice would be 

        (x) => not List.Contains(lst, x[YourColumn])
  • Mikee5's avatar
    Mikee5
    Regular Visitor
    3 years ago

    Thanks all for the fantastic responses. I have ended up using the list.buffer solution which works well.