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

     

slorin's avatar
slorin
Icon for Super User rankSuper 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