Advanced Power Query Pipeline Problem with Example File/Images

[ExcelWizard18]

This is for a financial model of a pipeline, and I’ve created a dummy data file for explanation purposes. I would say I’m a pretty advanced power user, but this one is a headscratcher for me. I found that others used filebin to share files so I've included a link, but I also uploaded images of the file.https://filebin.net/534tip1p43i3qhi4 

Overview: There are different pipeline “systems” (aka North/South) and each system has a max volume capacity that it can operate at. There are contracts that operate on each pipeline system (aka North A, North B, etc.), and they have a priority ranking that dictates the pecking order of capacity on the pipeline. The rank matters in the instance where there are more volumes than the system can handle, and the volumes will be reduced accordingly.

Context: The actual file has around 200 contracts, with 30+ systems, and has volume forecasts up to the year 2030, so there’s a lot of data. There’s also instances where one subsystem has 9-10 contracts, so it doesn’t seem efficient to build this using Boolean logic in Power Query (which is the only way I can think of).

The Ask: I’d like to recreate the Power Query output table using M code rather than excel formulas. In the model, this is something that would rarely be updated, and so I’d rather have all the compute upfront in power query. The output table will then feed other schedules that I’ve already built.

Any help on this would be greatly appreciated!

 

[OwenAuger · Accepted solution]

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?

[v-echaithra]

Hi @ExcelWizard18  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[v-echaithra]

Hi @ExcelWizard18 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @ExcelWizard18 ,

Thanks @dufoq3 , @v-prasare , @OwenAuger  for Addressing the issue.

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithra E.

[dufoq3]

Hi @ExcelWizard18, why there are different formulas for South vs North (Allocated calculation)?

North:

South:

[v-prasare · Accepted solution]

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

[OwenAuger · Accepted solution]

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?