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

     

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