Delete Rows based on conditions

[Mikee5]

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.

[Mikee5]

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

[AlienSx · Accepted solution]

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]

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]

@slorin thank you, mate! Better choice would be 

(x) => not List.Contains(lst, x[YourColumn])

[Anonymous]

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

[slorin]

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