Power Query中移除已退货的销售及对应退货交易行

需求说明 #

• 数据源为包含全量维度与事实数据的简易销售表，无需优化数据模型
• 销售数量列中，值为-1代表商品退货
• 需要移除所有退货行（数量=-1），同时删除对应的数量为+1的原销售行
• 禁止仅通过发票号过滤整组记录，因为同一发票可能包含未退货的有效商品，最终仅保留有效交易记录

补充示例 #

某发票包含3行记录：其中两行是同一商品的销售（数量+1）与退货（数量-1）记录，需删除这两行；另一行是确认有效的购买记录，需保留（对应示例中绿色行保留、黄色行删除）。

Power Query 解决方法 #

方法一：分组过滤法 #

1. 按发票号和商品ID（或唯一标识商品的字段）分组，计算每组数量总和并保留明细行

   = Table.Group(源, {"发票号", "商品ID"}, {{"数量总和", each List.Sum([销售数量]), type number}, {"明细行", each _, type table}})
   

2. 筛选出数量总和不为0的分组（总和为0的分组对应销售+退货的抵消记录，需剔除）

   = Table.SelectRows(上一步结果, each [数量总和] <> 0)
   

3. 展开筛选后的明细行，保留需要的字段

   = Table.ExpandTableColumn(上一步结果, "明细行", {"销售数量", "商品名称", "交易日期"}, {"销售数量", "商品名称", "交易日期"})
   

方法二：标记有效行法 #

1. 添加自定义列，计算当前行对应发票+商品的数量总和，标记是否为有效行

   = Table.AddColumn(源, "有效标记", each 
       let
           当前发票 = [发票号],
           当前商品 = [商品ID],
           数量总和 = List.Sum(Table.SelectRows(源, (行)=> 行[发票号]=当前发票 and 行[商品ID]=当前商品)[销售数量])
       in
           数量总和 <> 0
   )
   

2. 筛选出有效标记为true的行

   = Table.SelectRows(上一步结果, each [有效标记] = true)
   

3. 删除临时的有效标记列

   = Table.RemoveColumns(上一步结果, {"有效标记"})
   

内容的提问来源于stack exchange，提问作者Choonsik