Skip to content

Remove symmetrical records with Power Query or Python

unstuck

Is there a way to remove the yellow records with Power Query, Python?

They are errors and I need to get rid of them:

enter image description here

The process could be something like:

  1. Filter the table to retrieve the rows containing only the current “CustomerID”
  2. Check the Sales. Is there any value with -Sales?

And then delete those rows.

Advertisement

Answer

You may use following technique:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    add = Table.AddColumn(Source, "Abs", each Number.Abs([Sales])),
    group = Table.Group(add, {"Customer ID", "Abs"}, {{"sum", each List.Sum([Sales])},
                                                    {"all", each Table.RemoveColumns(_, "Abs")}}),
    filter = Table.SelectRows(group, each ([sum] <> 0)),
    final = Table.Combine(filter[all])
in
    final