如何在Power Query中按地点和产品类型周度计算滚动在手库存？

核心计算逻辑 #

期末库存 = 期初库存（或上周期末库存） + 收货量 - 发货量 + 退货量

• 分组维度：按Location和Product Type分组
• 计算顺序：按Week升序滚动计算

原始数据格式 #

预测数据（透视后） #

Location, Product Type, Week, Shipments, Receipts, Returns
A, B, 1, 54, 69, 8
A, B, 2, 98, 12, 3
A, B, 3, 68, 50, 3
A, C, 1, 9, 58, 9
A, C, 2, 95, 20, 5
A, C, 3, 93, 42, 10
D, B, 1, 27, 87, 7
D, B, 2, 5, 2, 4
D, B, 3, 92, 19, 4
D, C, 1, 96, 17, 5
D, C, 2, 50, 50, 7
D, C, 3, 70, 95, 4

期初库存数据 #

Location, Product Type, Starting Inventory
A, B, 116
A, C, 117
D, B, 108
D, C, 197

期望结果格式 #

Location, Product Type, Week, Shipments, Receipts, Returns, Ending Inventory
A, B, 1, 54, 69, 8, 139
A, B, 2, 98, 12, 3, 56
A, B, 3, 68, 50, 3, 41
A, C, 1, 9, 58, 9, 175
A, C, 2, 95, 20, 5, 105
A, C, 3, 93, 42, 10, 64
D, B, 1, 27, 87, 7, 175
D, B, 2, 5, 2, 4, 176
D, B, 3, 92, 19, 4, 107
D, C, 1, 96, 17, 5, 123
D, C, 2, 50, 50, 7, 130
D, C, 3, 70, 95, 4, 159

Power Query实现代码 #

let
    // 导入预测数据（替换为你的实际数据源）
    ForecastData = Table.FromRecords({
        [Location="A", Product Type="B", Week=1, Shipments=54, Receipts=69, Returns=8],
        [Location="A", Product Type="B", Week=2, Shipments=98, Receipts=12, Returns=3],
        [Location="A", Product Type="B", Week=3, Shipments=68, Receipts=50, Returns=3],
        [Location="A", Product Type="C", Week=1, Shipments=9, Receipts=58, Returns=9],
        [Location="A", Product Type="C", Week=2, Shipments=95, Receipts=20, Returns=5],
        [Location="A", Product Type="C", Week=3, Shipments=93, Receipts=42, Returns=10],
        [Location="D", Product Type="B", Week=1, Shipments=27, Receipts=87, Returns=7],
        [Location="D", Product Type="B", Week=2, Shipments=5, Receipts=2, Returns=4],
        [Location="D", Product Type="B", Week=3, Shipments=92, Receipts=19, Returns=4],
        [Location="D", Product Type="C", Week=1, Shipments=96, Receipts=17, Returns=5],
        [Location="D", Product Type="C", Week=2, Shipments=50, Receipts=50, Returns=7],
        [Location="D", Product Type="C", Week=3, Shipments=70, Receipts=95, Returns=4]
    }),
    // 导入期初库存数据（替换为你的实际数据源）
    StartingInventory = Table.FromRecords({
        [Location="A", Product Type="B", Starting Inventory=116],
        [Location="A", Product Type="C", Starting Inventory=117],
        [Location="D", Product Type="B", Starting Inventory=108],
        [Location="D", Product Type="C", Starting Inventory=197]
    }),
    // 合并期初库存到预测数据
    MergedData = Table.NestedJoin(ForecastData, {"Location", "Product Type"}, StartingInventory, {"Location", "Product Type"}, "StartingInventory", JoinKind.LeftOuter),
    ExpandedData = Table.ExpandTableColumn(MergedData, "StartingInventory", {"Starting Inventory"}, {"Starting Inventory"}),
    // 按地点和产品类型分组
    GroupedData = Table.Group(ExpandedData, {"Location", "Product Type"}, {
        {"GroupedRows", each _, type table [Location=text, Product Type=text, Week=number, Shipments=number, Receipts=number, Returns=number, Starting Inventory=number]}
    }),
    // 生成每组的滚动库存序列
    AddEndingInventory = Table.AddColumn(GroupedData, "WithEndingInventory", (group) =>
        let
            Rows = group[GroupedRows],
            SortedRows = Table.Sort(Rows, {"Week", Order.Ascending}),
            StartInv = SortedRows{0}[Starting Inventory],
            // 使用List.Generate计算滚动库存
            InventoryList = List.Generate(
                () => [Index=0, CurrentInv=StartInv + SortedRows{0}[Receipts] - SortedRows{0}[Shipments] + SortedRows{0}[Returns]],
                each [Index] < Table.RowCount(SortedRows),
                each [
                    Index = [Index] + 1,
                    CurrentInv = [CurrentInv] + SortedRows{[Index]}[Receipts] - SortedRows{[Index]}[Shipments] + SortedRows{[Index]}[Returns]
                ],
                each [CurrentInv]
            ),
            // 合并库存序列到原表格
            AddedInventory = Table.FromColumns(
                Table.ToColumns(SortedRows) & {InventoryList},
                Table.ColumnNames(SortedRows) & {"Ending Inventory"}
            )
        in
            AddedInventory
    ),
    // 展开分组数据并整理列顺序
    ExpandedResult = Table.ExpandTableColumn(AddEndingInventory, "WithEndingInventory", {"Week", "Shipments", "Receipts", "Returns", "Ending Inventory"}, {"Week", "Shipments", "Receipts", "Returns", "Ending Inventory"}),
    ReorderedColumns = Table.ReorderColumns(ExpandedResult, {"Location", "Product Type", "Week", "Shipments", "Receipts", "Returns", "Ending Inventory"})
in
    ReorderedColumns

代码说明 #

• List.Generate核心逻辑：
  1. 初始化：计算第一周的期末库存
  2. 终止条件：遍历完当前组的所有周数据
  3. 迭代：以上一周的期末库存为基础，计算本周期末库存
  4. 提取：输出每一周的期末库存值
• 分组处理确保每个Location+Product Type组合独立计算滚动库存

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