CODEWITHSUNDEEP
powerquery
haoran zhang
haoran zhang

Reputation: 51

Delete rows based on certain logic in power query

Sample Date

I need to delete rows based on the below logic:

Sum of column B for the same product, to compare with one of the values in column D for this product.

If the sum value < the value in column D, then delete the rows with extra ReceiptQty. In this case, for product AAA, receiptQty =12000, which is >10000, then delete the row 7.

Is there any way to achieve this in power query? Thanks~

Upvotes: 0

Views: 2792

Answers (1)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

This code should work:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    group = Table.Group(Source, {"ProductID"}, {"temp", each _}),
    list = Table.AddColumn(group, "list", each List.Skip(List.Accumulate([temp][ReceiptQty], {0}, (a, b) => a & {List.Last(a) + b}))),
    table = Table.AddColumn(list, "table", each Table.FromColumns(Table.ToColumns([temp])&{[list]}, Table.ColumnNames(Source)&{"RunningQty"})),
    final = Table.SelectRows(Table.Combine(table[table]), each [OnhandQty] >= [RunningQty])
in
    final

Upvotes: 1

Related Questions