ExcelWizard18
New Member
- Joined
- Mar 26, 2025
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
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. Filebin | 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!
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!
