I have a Power query challenge where I need full support on.
I want to allocate incoming purchase deliveries to sales lines which has a shipment priority, so end result will be a weekly shipment plan.

 

Here is a example of input and output:

 

Input table A (about 30K Sales lines, not 30k pcs)

Sales lines 21-123 – Prior.1 – Item A – 400 pcs

Sales lines 21-103 – Prior.2 – Item A – 415 pcs

Sales lines 21-023 – Prior.3 – Item A – 200 pcs

Sales lines 21-125 – Prior.4 – Item A – 350 pcs

 

Input table B (Incoming Purchasing orders.)

Weekly deliveries from Suppliers, due to lack of supplier capacity.

Item A – Week 202143 – 350 pcs

Item A – Week 202144 – 375 pcs

Item A – Week 202145 – 150 pcs

Item A – Week 202147 – 550 pcs

 

Output table C (Please ignore the blank rows below)

Sales lines 21-123 – Prior.1 – Item A – 400 pcs – Shipment 350 pcs – week 202143.    (Incoming Week 202143 – 350 pcs)

Sales lines 21-123 – Prior.1 – Item A – 400 pcs – Shipment 50 pcs – week 202144.      (Incoming Week 202144 – 375 pcs)

 

Sales lines 21-103 – Prior.2 – Item A – 415 pcs – Shipment 325 pcs – week 202144.     (Incoming Week 202144 – 375 pcs)

Sales lines 21-103 – Prior.2 – Item A – 415 pcs – Shipment   95 pcs – week 202145.     (Incoming Week 202145 – 150 pcs)

 

Sales lines 21-023 – Prior.3 – Item A – 200 pcs – Shipment  55 pcs – week 202145.     (Incoming Week 202145 – 150 pcs)

Sales lines 21-023 – Prior.3 – Item A – 200 pcs – Shipment 145 pcs – week 202147.     (Incoming Week 202147 – 550 pcs)

 

Sales lines 21-125 – Prior.4 – Item A – 350 pcs – Shipment 395 pcs – week 202147.     (Incoming Week 202147 – 550 pcs)

 

I have to use the Power Query Excel to solve this challenge as output Table C will be an input to another query

I think the task can be solved by Power Query functions below, but I am not sure and dont how to structure the entire M-code.

 

• List.generate (Iteration over Table A and use input from Table B , big challenge for me)
• Running Total in grouped tables https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query
• List.Transform({1..[SplitLines]})

Hope you can support on this