Fill order by priority power query

[tintin1012000]

Hello

I have attached a link to sample Link to file

I have a table from sales orders which shows product name along with the items required to build product, quantity required and in what order to build.
in table 2 i have an output table from Production.

i need to merge these tables but my problem is that some materials required to build product A are shared with product B and so on
i want the query to assign values based on priority so that i can see what materials im short

desired outcome is in sheet 3 of attached link

Thanks all

 

[sandy666]

what is the logic for this

SALES_REQUEST	MATERIAL	QTY	PRIORITY	QTY BY PRIORITY FILL
A	WIDGET1	10	1	10
B	WIDGET1	98	2	77
C	WIDGET1	123	3	0
D	WIDGET1	98	4	0
A	WIDGET2	10	1	10
B	WIDGET2	98	2	33
D	WIDGET2	98	4	0
A	WIDGET3	10	1	10
D	WIDGET3	98	4	14
A	WIDGET4	10	1	10
C	WIDGET4	123	3	55
B	WIDGET5	98	2	21
C	WIDGET6	123	3	88
C	WIDGET7	123	3	123
SPARE	WIDGET7	X	X	77

 

[tintin1012000]

what is the logic for this

B	WIDGET1	98	2	77
C	WIDGET1	123	3	0
D	WIDGET1	98	4	0

the logic is that widget 1 has a qty requirement of 10 parts which is fulfilled because its a number 1 priority
the remaining parts produced are given to widget1 priority 2. only 77 parts remaining
production department only produced a total of 87 widget 1

 

[sandy666]

maybe adapt Create Running Totals in Power Query

 

[tintin1012000]

Thanks Sandy, will try that now

 

[sandy666]

here is running total standard and running total start over (which you probably need because of priority)
instead of sum use difference

s666-PQ-RunningTotalStandard-RunningTotalGrouping.xlsm

Microsoft Excel Workbook

1drv.ms