Is there a way to remove the yellow records with Power Query, Python?
They are errors and I need to get rid of them:
The process could be something like:
- Filter the table to retrieve the rows containing only the current “CustomerID”
- 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