Advanced Power Query Pipeline Problem with Example File/Images

Hi ExcelWizard18 

Interesting problem! 🙂

I have attached an updated Excel workbook with one approach, and I am sure there are many possible variations!

One initial question:

• It looks like the volume allocated to South A on 1/01/2026 should be 40 rather than 20. Is that right?
  My Power Query method produced 40 (Capacity = 80 with South B Vol = 40 & South A Vol = 60). I haven't unpicked the Excel formulas to fully understand what's going on though.
• This was the only difference between my outputs and yours.

Summary of the process I followed in Power Query:

1. Create a function  fnAllocate whose purpose is to compute the allocation for one System on one Date.
   1. Input:
      1. A list of "Items" where each Item is a record representing one Contract on one Date with Volume.
      2. The name of the Volume field ("Volume" in this case).
      3. The Capacity
   2. Output:
      1. The original list of Items converted into a table, expanded with additional columns "Volume Type" and "Volume Output".
2. The main query Output does the following:
   1. Takes Volumes (source table).
   2. Joins Priority.
   3. Groups by System/Date, adding a Contracts column which is a nested table of all rows for each System/Date.
   4. Joins Capacity.
   5. Converts Contracts nested table into a list of records and sorts the list by Rank Priority.
   6. Applies fnAllocate to the Contracts column and stores result in Contracts Allocated column as a nested table.
   7. Selects just Contracts Allocated column, expands the nested table and keeps required columns.

fnAllocate function

The fnAllocate function looks like this. It uses List.Generate to step through the list of Contracts (which are assumed to have already been sorted) and calculate the relevant values. This doesn't necessarily need to convert the output to a table, but I thought it was convenient for what you're doing.

(Items as list, VolumeField as text, Capacity as number) as table =>
  let
    ItemCount = List.Count(Items),
    AllocationList = List.Generate(
      () => [
        Index                        = 0,
        CurrentVolume                = Record.Field(Items{Index}, VolumeField),
        VolumeAllocatedCumulativePre = 0,
        CapacityRemaining            = List.Max({0, Capacity - VolumeAllocatedCumulativePre}),
        VolumeAllocated              = List.Min({CapacityRemaining, CurrentVolume}),
        VolumeAllocatedCumulative    = VolumeAllocatedCumulativePre + VolumeAllocated,
        VolumeUnallocated            = CurrentVolume - VolumeAllocated
      ],
      each [Index] < ItemCount,
      each [
        Index                        = [Index] + 1,
        CurrentVolume                = Record.Field(Items{Index}, VolumeField),
        VolumeAllocatedCumulativePre = [VolumeAllocatedCumulative],
        CapacityRemaining            = List.Max({0, Capacity - VolumeAllocatedCumulativePre}),
        VolumeAllocated              = List.Min({CapacityRemaining, CurrentVolume}),
        VolumeAllocatedCumulative    = VolumeAllocatedCumulativePre + VolumeAllocated,
        VolumeUnallocated            = CurrentVolume - VolumeAllocated
      ],
      each Items{[Index]}
        & [
          Allocation = #table(
            type table [Volume Type = text, Volume Output = number],
            {{"Allocated", [VolumeAllocated]}, {"Overflow", [VolumeUnallocated]}}
          )
        ]
    ),
    AllocationTable = Table.FromRecords(AllocationList),
    AllocationTableExpanded = Table.ExpandTableColumn(AllocationTable, "Allocation", {"Volume Type", "Volume Output"})
  in
    AllocationTableExpanded

The Output table is loaded to the sheet at $J$73.

Notes:

• The rest of the M code is in the attached workbook so I won't paste it all here.
• Regardless of the exact method you use, I think an allocation function is the way to go.
• There is likely some handling of edge cases required that I haven't considered, such as zero capacity, no contracts for a given System.

Is this the sort of thing you were looking for?

Hi ExcelWizard18,

Thanks for reaching MS Fabric community support

As per your description, I understand that your goal is to calculate the allocated volumes (based on priority and capacity limits) and overflow volumes, which will then be shown in the output table.

Here’s a structured approach to implement this in Power Query:

1. Load Data into Power Query
First, you’ll need to load the Capacity Table, Priority Table, and Volumes Table into Power Query.

2. Merge the Tables
Join the tables on relevant fields to bring together all the required information in one table. The steps are as follows:

Join the Volumes Table with the Priority Table: Use the Contract column to join the Volumes Table with the Priority Table to get the System and Priority Rank.

Join with the Capacity Table: Join the resulting table from Step 1 with the Capacity Table on the System column to get the system's Capacity.

3. Sort by Priority
Sort the resulting table by System and Priority Rank in ascending order because you need to allocate volumes in priority order.

4. Add the Allocated Volume Column
Now, we will calculate the Allocated Volume for each contract in the Volumes Table based on the system's capacity.

Click on Add Column > Custom Column.
if [Volume] <= [Capacity] then [Volume] else [Capacity]
The above formula checks if the Volume of the contract is less than or equal to the system’s Capacity. If it is, it allocates the full contract volume. If not, it allocates the remaining system capacity.

5. Calculate the Remaining Capacity for Each Row
To properly manage the remaining capacity, we'll need to create a column that keeps track of the remaining capacity for each contract in a system.
Click Add Column > Custom Column again.
[Capacity] - [Allocated Volume]
This will subtract the Allocated Volume from the Capacity to track how much capacity remains for subsequent contracts.

6. Calculate the Overflow Volume
If a contract cannot be fully allocated due to capacity limits, we need to calculate the Overflow Volume (i.e., the volume that is not allocated).
if [Volume] > [Allocated Volume] then [Volume] - [Allocated Volume] else 0
This formula calculates the overflow for each contract based on the remaining volume after the allocation.

7. Unpivot the Allocated and Overflow Volumes
Unpivot the Allocated and Overflow Volumes to create two rows for each contract: one for the Allocated Volume and one for the Overflow.
This will turn the Allocated Volume and Overflow Volume into rows, and the new Volume Type column will show "Allocated" or "Overflow" for each row.

8. Load Data to Power BI

Thanks,

Prashanth Are