Power Query中如何计算满足各需求的首个充足供应日期？

要实现你描述的需求——为每个商品的每笔需求找到首个可满足的供应日期，同时更新剩余可用供应用于后续需求匹配，可通过以下步骤完成：

核心思路 #

1. 按商品唯一标识分组，单独处理每个商品的供需数据
2. 对每个商品的需求和供应分别按日期排序（确保按时间顺序处理）
3. 迭代每笔需求，从当前剩余供应和未使用的供应数据中累加，直到找到首个能满足需求的供应日期，同时更新剩余供应和已使用的供应位置

步骤1：创建自定义匹配函数 #

在Power Query中新建一个自定义函数，用于处理单个商品的供需匹配：

let
    MatchDemandToSupply = (demandTable as table, supplyTable as table) as table =>
    let
        // 按日期排序需求和供应数据
        SortedDemand = Table.Sort(demandTable, {"需求日期", Order.Ascending}),
        SortedSupply = Table.Sort(supplyTable, {"供应日期", Order.Ascending}),
        // 将供应表转换为列表，方便迭代
        SupplyList = Table.ToRecords(SortedSupply),
        // 初始化状态：剩余可用供应、当前供应索引、结果集合
        InitialState = [Remaining = 0, SupplyPos = 0, Results = {}],
        // 遍历每笔需求
        ProcessedData = List.Accumulate(Table.ToRecords(SortedDemand), InitialState, (state, currentDemand) =>
            let
                NeedQty = currentDemand[需求数量(need)],
                CurrentRemain = state[Remaining],
                StartPos = state[SupplyPos],
                // 累加供应直到满足需求
                AccumulateSupply = List.Generate(
                    // 初始状态：检查剩余供应是否已满足需求
                    () => [
                        Pos = StartPos,
                        Total = CurrentRemain,
                        MeetDate = if CurrentRemain >= NeedQty then SupplyList{StartPos}[供应日期] else null
                    ],
                    // 循环条件：累计量未满足需求，且还有未遍历的供应
                    each [Total] < NeedQty and [Pos] < List.Count(SupplyList),
                    // 迭代逻辑：移动到下一个供应，累加数量，记录当前供应日期
                    each [
                        Pos = [Pos] + 1,
                        Total = [Total] + SupplyList{[Pos]-1}[供应数量],
                        MeetDate = SupplyList{[Pos]-1}[供应日期]
                    ],
                    each _
                ),
                // 获取满足需求的最终状态
                FinalState = List.Last(AccumulateSupply),
                // 更新剩余供应和下一次迭代的起始位置
                NewRemain = FinalState[Total] - NeedQty,
                NewPos = FinalState[Pos],
                // 构造结果记录
                ResultRecord = Record.Combine({
                    currentDemand,
                    [满足日期 = FinalState[MeetDate], 剩余可用供应 = NewRemain]
                })
            in
                [Remaining = NewRemain, SupplyPos = NewPos, Results = state[Results] & {ResultRecord}]
        ),
        // 将结果集合转换为表格
        ResultTable = Table.FromRecords(ProcessedData[Results])
    in
        ResultTable
in
    MatchDemandToSupply

步骤2：应用函数到整个数据集 #

假设你的数据源包含商品ID、需求日期、需求数量(need)、供应日期、供应数量(Supply)字段（如果是宽表需先拆分为供需表，长表则直接分组），在主查询中执行以下操作：

let
    // 替换为你的实际数据源
    Source = Excel.CurrentWorkbook(){[Name="你的数据表名"]}[Content],
    // 按商品ID分组，拆分需求和供应数据（如果是长表，需先筛选供需类型）
    GroupedByProduct = Table.Group(Source, {"商品ID"}, {
        {"需求表", each Table.SelectRows(_, not List.IsNull(_[需求数量(need)])), type table},
        {"供应表", each Table.SelectRows(_, not List.IsNull(_[供应数量(Supply)])), type table}
    }),
    // 调用自定义函数匹配供需
    MatchedResults = Table.AddColumn(GroupedByProduct, "匹配结果", each MatchDemandToSupply([需求表], [供应表])),
    // 展开匹配结果表
    ExpandedResults = Table.ExpandTableColumn(MatchedResults, "匹配结果", Table.ColumnNames(Table.First(MatchedResults[匹配结果])))
in
    ExpandedResults

关键逻辑说明 #

• 使用List.Accumulate维护迭代状态：每次处理完一笔需求后，记录剩余未使用的供应数量和下一次需要开始遍历的供应位置，避免重复计算
• List.Generate用于逐个累加供应数量，直到累计量（含剩余供应）满足当前需求，此时的供应日期即为首个可满足需求的日期
• 剩余供应会自动带入下一笔需求的计算，完全符合你描述的「6-5=1，下一笔需求累计供应为1+100=101」的逻辑

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