仓库水桶配水优化Power Query代码错误修正及多仓库适配

架构老林

2026-6-13

修正Power Query水桶配水逻辑问题

场景描述

现有2个Depot配备不同容积的预填充水桶，各Depot有指定AllotedWater。Volume Utilisation定义为桶内水量/桶容积，目标是在配水量约束下最大化每个水桶的Volume Utilisation。
填充逻辑：低利用率水桶优先填充，当多个水桶达到相同利用率后同步填充，直至所有水桶满容或配水量耗尽。

输入数据

水桶关联Depot数据：

DepotID    BucketID     Volumeofbucket     PrefillValue       Volumeutilised   
Depot 1    Bucket 1           100        50         50%  
Depot 1    Bucket 2           1000       600        60%  
Depot 1    Bucket 3           100        20         20%  
Depot 1    Bucket 4           10         9          90% 
Depot 1    Bucket 5           10000      9000       90%   
Depot 1    Bucket 6           1000       300        30%   
Depot 1    Bucket 7           100        40         40%   
Depot 2    Bucket 1           200        55         28%   
Depot 2    Bucket 2           400        222        56%    
Depot 2    Bucket 3           50         11         22%   
Depot 2    Bucket 4           4000       600        15%

Depot配水量数据：

DepotID        AllotedWater        
Depot 1        900       
Depot 2        400

问题说明

当前使用的Power Query代码仅处理Depot 1，且存在逻辑错误：执行14步后，Depot 1的Bucket 2利用率停留在60%，而Bucket 6被填充至90%，未按同利用率同步填充的逻辑分配；同时代码无法适配所有Depot。

现有代码

let
Source = Excel.Workbook(File.Contents("/Users/kumarsubhendu/Desktop//Water.xlsx"), null, true),
#"Navigation 1" = Source{[Item = "Bucket IDs", Kind = "Sheet"]}[Data],
#"Promoted headers" = Table.PromoteHeaders(#"Navigation 1", [PromoteAllScalars = true]),
#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"DepotID", type text}, {"BucketID", type text}, {"Volumeofbucket", Int64.Type}, {"PrefillValue", Int64.Type}, {"Volumeutilised", type number}}),
#"Added Custom3" = Table.AddColumn(#"Changed column type", "CurrentValue", each [PrefillValue]),
#"Added Custom" = Table.AddColumn(#"Added Custom3", "Step", each 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([DepotID] = "Depot 1")),
NextLevel = (tbl)=>
let
#"Added Custom" = Table.AddColumn(Table.Buffer(tbl), "Percent", each [CurrentValue]/[Volumeofbucket],Percentage.Type),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Percent", Order.Ascending},{"Volumeofbucket", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "NextHighest Percent", (k)=> List.Min(Table.SelectRows(#"Added Index",each [Percent]>k[Percent])[Percent]) ?? 1,Percentage.Type),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Delta", each if [Index]=0 then [Volumeofbucket]*([NextHighest Percent]-[Percent]) else 0,Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom2",each [CurrentValue],each [CurrentValue]+[Delta] ,Replacer.ReplaceValue,{"CurrentValue"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value",each [Step],each [Step]+1 ,Replacer.ReplaceValue,{"Step"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value2",{"Index", "Percent", "NextHighest Percent", "Delta"})
in
#"Removed Columns",
Levels = List.Generate(
() => #"Filtered Rows",
each List.Sum(_[CurrentValue])<900+List.Sum(#"Filtered Rows"[PrefillValue]),
each NextLevel(_)
),
#"Converted to Table" = Table.FromList(Levels, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"DepotID", "BucketID", "Volumeofbucket", "PrefillValue", "CurrentValue", "Step"}, {"DepotID", "BucketID", "Volumeofbucket", "PrefillValue", "CurrentValue", "Step"}),
#"Added custom 1" = Table.TransformColumnTypes(Table.AddColumn(#"Expanded Column1", "VolumeUtilised", each [CurrentValue] / [Volumeofbucket]), {{"VolumeUtilised", Percentage.Type}}),
#"Filtered rows 1" = Table.SelectRows(#"Added custom 1", each ([Step] = 0 or [Step] = 14)),
#"Sorted rows" = Table.Sort(#"Filtered rows 1", {{"BucketID", Order.Ascending}, {"Step", Order.Ascending}})
in
#"Sorted rows"

修正后的代码

let
    // 读取数据源
    Source = Excel.Workbook(File.Contents("/Users/kumarsubhendu/Desktop//Water.xlsx"), null, true),
    BucketSheet = Source{[Item = "Bucket IDs", Kind = "Sheet"]}[Data],
    AllotmentSheet = Source{[Item = "Depot Allotment", Kind = "Sheet"]}[Data], // 假设配水量在名为"Depot Allotment"的工作表
    
    // 处理水桶数据
    BucketTable = Table.PromoteHeaders(BucketSheet, [PromoteAllScalars = true]),
    BucketTableTyped = Table.TransformColumnTypes(BucketTable, {
        {"DepotID", type text}, {"BucketID", type text}, 
        {"Volumeofbucket", Int64.Type}, {"PrefillValue", Int64.Type}, 
        {"Volumeutilised", type number}
    }),
    BucketTableWithCurrent = Table.AddColumn(BucketTableTyped, "CurrentValue", each [PrefillValue]),
    
    // 处理配水量数据
    AllotmentTable = Table.PromoteHeaders(AllotmentSheet, [PromoteAllScalars = true]),
    AllotmentTableTyped = Table.TransformColumnTypes(AllotmentTable, {
        {"DepotID", type text}, {"AllotedWater", Int64.Type}
    }),
    
    // 按Depot分组处理
    GroupedByDepot = Table.Group(BucketTableWithCurrent, {"DepotID"}, {
        {"BucketData", each _, type table [DepotID=text, BucketID=text, Volumeofbucket=Int64.Type, PrefillValue=Int64.Type, Volumeutilised=number, CurrentValue=Int64.Type]},
        {"TotalPrefill", each List.Sum(_[PrefillValue]), Int64.Type}
    }),
    // 合并配水量数据
    MergedAllotment = Table.NestedJoin(GroupedByDepot, {"DepotID"}, AllotmentTableTyped, {"DepotID"}, "AllotmentData", JoinKind.Inner),
    MergedAllotmentExpanded = Table.ExpandTableColumn(MergedAllotment, "AllotmentData", {"AllotedWater"}, {"AllotedWater"}),
    
    // 定义单个Depot的填充逻辑函数
    FillDepotBuckets = (depotData as table, totalAlloted as number, totalPrefill as number) as table =>
    let
        MaxTotal = totalPrefill + totalAlloted,
        // 生成填充步骤的递归函数
        NextFillStep = (currentTable as table) as table =>
        let
            // 计算当前利用率，过滤已装满的桶
            WithUtilization = Table.AddColumn(currentTable, "Utilization", each [CurrentValue]/[Volumeofbucket]),
            FilteredNonFull = Table.SelectRows(WithUtilization, each [Utilization] < 1),
            // 按利用率升序排序，相同利用率的桶放在一起
            SortedBuckets = Table.Sort(FilteredNonFull, {{"Utilization", Order.Ascending}}),
            // 分组相同利用率的桶
            GroupedByUtil = Table.Group(SortedBuckets, {"Utilization"}, {
                {"Buckets", each _, type table [BucketID=text, Volumeofbucket=Int64.Type, CurrentValue=Int64.Type, Utilization=number]},
                {"TotalCapacity", each List.Sum([Volumeofbucket]), Int64.Type},
                {"TotalCurrent", each List.Sum([CurrentValue]), Int64.Type}
            }),
            // 获取当前最低利用率组
            LowestUtilGroup = Table.First(GroupedByUtil),
            // 计算下一个目标利用率（下一组的利用率或100%）
            NextUtil = if Table.RowCount(GroupedByUtil) > 1 then GroupedByUtil{1}[Utilization] else 1,
            // 计算将当前组填充到下一个利用率所需的总水量
            RequiredWater = (NextUtil - LowestUtilGroup[Utilization]) * LowestUtilGroup[TotalCapacity],
            // 计算当前剩余可分配水量
            CurrentTotal = List.Sum(currentTable[CurrentValue]),
            RemainingWater = MaxTotal - CurrentTotal,
            
            // 确定实际填充的水量
            ActualWater = if RequiredWater <= RemainingWater then RequiredWater else RemainingWater,
            // 计算每个桶的增量
            DeltaPerBucket = if RequiredWater <= RemainingWater then 
                (NextUtil - LowestUtilGroup[Utilization]) * [Volumeofbucket] 
            else 
                ActualWater * [Volumeofbucket] / LowestUtilGroup[TotalCapacity],
            
            // 更新当前组的水量
            UpdatedLowestGroup = Table.AddColumn(LowestUtilGroup[Buckets], "NewCurrentValue", each [CurrentValue] + DeltaPerBucket),
            UpdatedLowestGroupTyped = Table.TransformColumnTypes(UpdatedLowestGroup, {{"NewCurrentValue", Int64.Type}}),
            // 合并更新后的桶和未更新的桶
            UpdatedBuckets = Table.Combine({
                UpdatedLowestGroupTyped,
                Table.SelectRows(currentTable, each [CurrentValue]/[Volumeofbucket] > LowestUtilGroup[Utilization])
            }),
            // 替换CurrentValue并移除临时列
            FinalUpdated = Table.RenameColumns(UpdatedBuckets, {{"NewCurrentValue", "CurrentValue"}}),
            FinalUpdatedClean = Table.RemoveColumns(FinalUpdated, {"Utilization"})
        in
            FinalUpdatedClean,
        
        // 生成所有填充步骤
        FillSteps = List.Generate(
            () => depotData,
            each List.Sum(_[CurrentValue]) < MaxTotal and List.AnyTrue(Table.AddColumn(_, "IsNotFull", each [CurrentValue] < [Volumeofbucket])[IsNotFull]),
            each NextFillStep(_)
        ),
        // 获取最终状态
        FinalState = List.Last(FillSteps),
        // 添加利用率列
        FinalStateWithUtil = Table.AddColumn(FinalState, "VolumeUtilised", each [CurrentValue]/[Volumeofbucket], Percentage.Type)
    in
        FinalStateWithUtil,
    
    // 应用填充逻辑到每个Depot
    ProcessedDepots = Table.AddColumn(MergedAllotmentExpanded, "FinalBucketData", each FillDepotBuckets([BucketData], [AllotedWater], [TotalPrefill])),
    // 展开最终结果
    ExpandedFinalData = Table.ExpandTableColumn(ProcessedDepots, "FinalBucketData", {"BucketID", "Volumeofbucket", "PrefillValue", "CurrentValue", "VolumeUtilised"}, {"BucketID", "Volumeofbucket", "PrefillValue", "CurrentValue", "VolumeUtilised"}),
    // 整理列顺序并排序
    ReorderedColumns = Table.ReorderColumns(ExpandedFinalData, {"DepotID", "BucketID", "Volumeofbucket", "PrefillValue", "CurrentValue", "VolumeUtilised"}),
    SortedResult = Table.Sort(ReorderedColumns, {{"DepotID", Order.Ascending}, {"BucketID", Order.Ascending}})
in
    SortedResult

修正说明

适配多Depot处理：通过Table.Group按Depot分组，结合配水量数据，对每个Depot独立执行填充逻辑，不再局限于单个Depot。
修复同步填充逻辑：
- 对相同利用率的桶进行分组，计算将整组填充到下一个利用率所需的总水量，确保同组桶同步填充。
- 当剩余水量不足以填满整组时，按桶的容积比例分配剩余水量，保证利用率同步提升。
优化终止条件：同时检查总水量是否耗尽、是否还有未装满的桶，避免无效循环。
清理冗余代码：移除固定步骤筛选，直接输出最终填充状态，更贴合实际需求。

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

仓库水桶配水优化Power Query代码错误修正及多仓库适配

场景描述 #

输入数据 #

水桶关联Depot数据： #

Depot配水量数据： #

问题说明 #

现有代码 #

修正后的代码 #

修正说明 #