Power Query入库库存分配至未结订单模型构建求助

数据结构参考 #

先明确两张核心表的标准结构（可根据你的实际数据调整列名）：

• 未结订单表（示例命名为OpenOrders）：

  SKU	未结订单数量
  A001	150
  B002	80

• 入库库存表（示例命名为IncomingStock）：

  SKU	可用日期	入库数量
  A001	2024-05-01	50
  A001	2024-05-03	70
  A001	2024-05-05	60
  B002	2024-05-02	100

实现步骤 #

1. 预处理数据 #

• 对IncomingStock按SKU升序、可用日期升序排序，确保库存按时间先后顺序分配。
• 确保OpenOrders中每个SKU唯一（若有重复，先按SKU分组求和未结订单数量）。

2. 核心M代码实现 #

在Power Query编辑器中新建空白查询，粘贴以下代码（替换表名和列名以匹配你的实际数据）：

let
    // 加载数据源
    Source_Orders = Excel.CurrentWorkbook(){[Name="OpenOrders"]}[Content],
    Source_Stock = Excel.CurrentWorkbook(){[Name="IncomingStock"]}[Content],
    
    // 库存表排序并按SKU分组
    Sorted_Stock = Table.Sort(Source_Stock,{{"SKU", Order.Ascending}, {"可用日期", Order.Ascending}}),
    Grouped_Stock = Table.Group(Sorted_Stock, {"SKU"}, {{"库存明细", each _, type table [SKU=text, 可用日期=date, 入库数量=number]}}),
    
    // 合并订单与库存分组表
    Merged_Tables = Table.NestedJoin(Source_Orders, {"SKU"}, Grouped_Stock, {"SKU"}, "库存明细", JoinKind.LeftOuter),
    
    // 定义迭代分配函数
    AllocateStock = (orderQty as number, stockList as list) as record =>
        let
            // 初始化迭代变量
            InitialState = [剩余需求=orderQty, 已分配总量=0, 最后分配日期=null],
            // 用List.Accumulate实现循环分配逻辑
            IteratedState = List.Accumulate(stockList, InitialState, 
                (currentState, stockRecord) =>
                    let
                        本次分配量 = List.Min({currentState[剩余需求], stockRecord[入库数量]}),
                        新剩余需求 = currentState[剩余需求] - 本次分配量,
                        新已分配总量 = currentState[已分配总量] + 本次分配量
                    in
                        [剩余需求=新剩余需求, 已分配总量=新已分配总量, 最后分配日期=stockRecord[可用日期]]
            ),
            // 整理最终结果
            完全分配 = IteratedState[剩余需求] <= 0,
            结果日期 = IteratedState[最后分配日期]
        in
            [已分配数量=IteratedState[已分配总量], 分配状态日期=结果日期, 是否完全分配=完全分配],
    
    // 为每行订单应用分配函数
    Added_Allocation = Table.AddColumn(Merged_Tables, "分配结果", each if [库存明细] <> null then AllocateStock([未结订单数量], Table.ToRecords([库存明细])) else [已分配数量=0, 分配状态日期=null, 是否完全分配=false]),
    
    // 展开分配结果列
    Expanded_Result = Table.ExpandRecordColumn(Added_Allocation, "分配结果", {"已分配数量", "分配状态日期", "是否完全分配"}, {"已分配数量", "分配状态日期", "是否完全分配"}),
    
    // 清理冗余列
    Final_Table = Table.RemoveColumns(Expanded_Result, {"库存明细"})
in
    Final_Table

3. 关键逻辑说明 #

• 用List.Accumulate替代VBA循环，实现按可用日期顺序的迭代分配，自动处理每个SKU的库存分配流程。
• 每次迭代取「当前剩余订单量」和「当前入库库存」的较小值作为分配量，实时更新剩余需求、已分配总量和最后分配日期。
• 最终根据剩余需求是否为0判断是否完全分配，返回对应日期。

4. 适配调整 #

• 替换代码中的OpenOrders、IncomingStock为你的实际表名。
• 若列名不同（比如将「未结订单数量」改为「需求总量」），统一替换代码中对应的列名即可。

方案优势 #

• 无需手动维护索引或辅助列，入库表每日更新后，刷新查询即可自动重新计算分配结果。
• 基于Power Query函数式编程逻辑，稳定性远高于手动维护的辅助列方案，适配数据更新场景。

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