Power Query M代码实现管道合同容量优先级分配计算需求

Problem Overview #

• Handle capacity allocation across 30+ pipeline systems with 200+ contracts (each tied to a system and assigned a priority ranking)
• For each year up to 2030, allocate system capacity to contracts:
  • If total contract demand ≤ system maximum capacity: full demand is allocated to all contracts
  • If total demand exceeds capacity: allocate capacity to contracts in priority order until the system's maximum capacity is exhausted
• Replace inefficient Excel boolean logic with optimized M code to handle large datasets, outputting a ready-to-use table for downstream scheduling modules

Solution Approach #

We'll use grouping by pipeline system and year, combined with efficient list operations to calculate cumulative demand and prioritize allocation. Here's the step-by-step M code implementation:

Step 1: Load and Prepare Source Data #

Assume your source tables are named Contracts (contains System, Contract ID, Priority, Year, Demand) and SystemCapacities (contains System, Year, MaxCapacity):

let
    // Load source tables (adjust references to match your workbook)
    SourceContracts = Excel.CurrentWorkbook(){[Name="Contracts"]}[Content],
    SourceCapacities = Excel.CurrentWorkbook(){[Name="SystemCapacities"]}[Content],
    
    // Set correct data types to avoid calculation errors
    Contracts = Table.TransformColumnTypes(SourceContracts, {
        {"System", type text},
        {"Contract ID", type text},
        {"Priority", Int64.Type},
        {"Year", Int64.Type},
        {"Demand", Currency.Type}
    }),
    SystemCapacities = Table.TransformColumnTypes(SourceCapacities, {
        {"System", type text},
        {"Year", Int64.Type},
        {"MaxCapacity", Currency.Type}
    })

Step 2: Join Data and Group by System + Year #

Join contracts with their corresponding system capacity data, then group to process each system-year combination independently:

// Join contracts with their system's yearly maximum capacity
    JoinedData = Table.NestedJoin(Contracts, {"System", "Year"}, SystemCapacities, {"System", "Year"}, "SystemCapacity", JoinKind.Inner),
    ExpandCapacity = Table.ExpandTableColumn(JoinedData, "SystemCapacity", {"MaxCapacity"}, {"MaxCapacity"}),
    
    // Group by System and Year, sort contracts in each group by priority (lowest number = highest priority)
    Grouped = Table.Group(ExpandCapacity, {"System", "Year", "MaxCapacity"}, {
        {"SortedContracts", each Table.Sort(_, {{"Priority", Order.Ascending}}), type table}
    })

Step 3: Calculate Allocated Capacity #

For each grouped system-year, compute cumulative demand and determine allocated capacity using efficient list operations:

// Add allocation logic to each group
    AddAllocation = Table.AddColumn(Grouped, "AllocatedContracts", (group) =>
        let
            ContractsSorted = group[SortedContracts],
            DemandList = ContractsSorted[Demand],
            // Calculate cumulative demand for sorted contracts
            CumulativeDemand = List.Accumulate(DemandList, {}, (state, current) => state & {List.Sum(state) + current}),
            // Compute allocated capacity per contract
            AllocatedList = List.Generate(
                () => [Idx=0, PrevCumul=0, Allocated=if CumulativeDemand{0} <= group[MaxCapacity] then DemandList{0} else group[MaxCapacity]],
                each [Idx] < Table.RowCount(ContractsSorted),
                each [
                    Idx = [Idx] + 1,
                    PrevCumul = CumulativeDemand{[Idx]-1},
                    CurrentCumul = CumulativeDemand{[Idx]},
                    Allocated = if PrevCumul >= group[MaxCapacity] then 0 
                               else if CurrentCumul <= group[MaxCapacity] then DemandList{[Idx]} 
                               else group[MaxCapacity] - PrevCumul
                ],
                each [Allocated]
            ),
            // Append allocated capacity column to contracts table
            WithAllocation = Table.AddColumn(ContractsSorted, "AllocatedCapacity", each AllocatedList{Table.PositionOf(ContractsSorted, _)})
        in
            WithAllocation
    ),
    
    // Expand the allocated contracts table and clean up
    ExpandAllocated = Table.ExpandTableColumn(AddAllocation, "AllocatedContracts", {"Contract ID", "Priority", "Demand", "AllocatedCapacity"}, {"Contract ID", "Priority", "Demand", "AllocatedCapacity"}),
    FinalTable = Table.RemoveColumns(ExpandAllocated, {"SortedContracts"})
in
    FinalTable

Key Optimizations #

• Grouped Processing: Isolates each system-year combination to minimize redundant calculations
• Sorted Priority: Contracts are sorted once per group, avoiding repeated sorting operations
• List Operations: Uses List.Accumulate and List.Generate instead of row-by-row checks, drastically improving performance for large datasets
• Early Termination: Contracts after the capacity threshold automatically get 0 allocation without extra computations

Notes #

• If your priority ranking uses higher numbers for higher priority, adjust the sort order in Table.Sort to Order.Descending
• Validate with a small subset of data first to ensure allocation matches your Excel formula results
• The code assumes one capacity entry per system per year; modify the join logic if your capacity data has different granularity

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