PowerQuery中如何按商品计算变更后库存（替代Excel上行引用逻辑）

需求说明 #

作为PowerQuery新手，需要处理商品预期库存变更数据：入库时库存增加，出库时库存减少，最终生成包含商品编码、当前库存、变更量、变更后库存的列表。

数据示例 #

注：因是预期未来数据，每个商品的当前库存固定。

Excel实现逻辑 #

在Excel中可通过公式实现：

D2 = IF(A1=A2;D1+C2;B2+C2)

逻辑：相同商品时引用上一行的变更后库存+当前变更量；不同商品时用当前库存+变更量。

尝试的失败方法 #

在PowerQuery中尝试过添加索引列、合并表格、创建条件列"Häufigkeit"，并用以下代码计算但失败：

= Table.AddColumn(#"addcolum", "ConditionalColumn", each if [Häufigkeit]="Double" then ([colum4]{-1}+[colum3]) else [colum2+colum3])

请问该如何解决此问题？

在PowerQuery中要实现这个逻辑，核心是按商品分组后计算累积值，因为需要同一商品内按顺序累加变更量，且初始值为当前库存。以下两种方法都可以实现需求：

方法一：分组计算累积和（高效） #

适合数据量较大的场景，通过分组后计算变更量的累积和来得到结果：

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    // 按商品编码排序（确保同商品记录连续，已有正确顺序可跳过）
    排序 = Table.Sort(源,{{"Article", Order.Ascending}}),
    // 按商品和当前库存分组，计算每组内的变更后库存
    分组计算 = Table.Group(排序, {"Article", "actual stock"}, {
        {"变更记录", (each 
            let
                添加索引 = Table.AddIndexColumn(_, "索引", 0, 1),
                // 用初始库存 + 从第0行到当前行的变更量总和
                添加变更后库存 = Table.AddColumn(添加索引, "stock after change", 
                    each [actual stock] + List.Sum(List.Range([amount of change], 0, [索引]+1))
                ),
                移除索引 = Table.RemoveColumns(添加变更后库存, {"索引"})
            in
                移除索引
        ), type table}}),
    // 展开分组后的记录
    展开记录 = Table.ExpandTableColumn(分组计算, "变更记录", {"amount of change", "stock after change"})
in
    展开记录

方法二：逐行判断（复刻Excel逻辑） #

完全对应Excel的公式逻辑，逐行检查上一行是否为同商品，新手更容易理解：

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    排序 = Table.Sort(源,{{"Article", Order.Ascending}}),
    添加索引 = Table.AddIndexColumn(排序, "行索引", 0, 1),
    // 逐行计算变更后库存
    计算库存 = Table.AddColumn(添加索引, "stock after change", (行) =>
        let
            之前行 = Table.FirstN(添加索引, 行[行索引]),
            // 找到最后一条同商品的历史记录
            上一个同商品 = List.Last(Table.SelectRows(之前行, each [Article] = 行[Article])),
            // 无历史记录则用初始库存+变更量，否则用上一行结果+变更量
            结果 = if 上一个同商品 = null then 行[actual stock] + 行[amount of change] else 上一个同商品[stock after change] + 行[amount of change]
        in
            结果
    ),
    移除索引 = Table.RemoveColumns(计算库存, {"行索引"})
in
    移除索引

注意事项 #

• 必须保证同一商品的变更记录按预期顺序排列，否则计算结果会出错；
• 方法一效率更高，推荐用于大数据量场景；方法二更直观，适合新手理解逻辑。

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