Remove symmetrical records with Power Query or Python

user_15235239 0
Viewed 38

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.

powerquery python
2 Answers

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
user_107870500

in SQL you could do something like this:

SELECT *
FROM table_name
WHERE customer_id = 123667
    AND sales365D >= 0

or in Python you could import with the pandas library and filter like this:

import pandas as pd

df = pd.from_excel('filename.xlsx')
df = df[(df.customer_id == 123667) & (df.sales365D >= 0)].copy()
df.to_excel('filename_w_no_errors.xlsx')
user_47801670
Related