Power Query - Data manipulation

[mddistributor1]

I have data setup like the below table. Every time something is received the system creates a dash that is one higher than the prior one which shows everything that is yet to be received. I'm trying to calculate backorders by month, but I need the data setup differently. I could use some help on how this might be possible. 

VendorID	PoNum	PODate	PO Amount	Received amount	Received Date
1	123	4/2/2022	$1,000
1	123-1	4/2/2022	$1,000	$100	4/8/2022
1	123-2	4/2/2022	$1,000	$200	5/25/2022
1	123-3	4/2/2022	$1,000	$700	6/3/2022
1	125	5/6/2022	$1,000
1	125-1	5/6/2022	$1,000	$700	5/23/2022
1	125-2	5/6/2022	$1,000	$200	7/6/2022

I'd like to get to the table below. You'll notice on PO 125 that the same amount is in May and June because the backorder amount hasn't changed for June, but there is no received date for that month in the original data. 

VendorID	PoNum	Month End	Backorder Amount (PO Amount - Received)
1	123-1	4/30/2022	$900
1	123-2	5/31/2022	$700
1	123-3	6/30/2022	$0
1	125-1	5/31/2022	$300
1	125-1	6/31/2022	$300
1	125-2	7/31/2022	$100

[CNENFRNL]

Wierd to use PQ to solve such a question whereas DAX is designed to come into play in such a scenario. A DAX solution is way more concise and elegant.

For fun only, showcase of powerful worksheet formula,

[mddistributor1]

This is an awesome setup. I wouldn't have come up with that. Do you have any way to get that extra date in there? Because June doesn't have anything showing for backorders even though there was $300 in backorders that month. This was originally why I was thinking power query because I wanted the actual data to have something for each date. If that is possible in DAX I'd love to know how to do it. 

[CNENFRNL]

A seemly rookie question but, in fact, fairly tricky.

[mddistributor1]

Now, here is the question. How do you aggregate this for all POs? Because I do want to be able to drill to that level, but most often I'd be looking at the vendor level or just overall backorder amount and tracking that. Again this is why my initial thought was Power Query. 

[wdx223_Daniel]

= #table({"VendorID","PoNum","Month End","Backorder Amount"},List.TransformMany(Table.Group(Source,{"VendorID","PoNum"},{"n",each Table.ToRows(Table.Sort(_,"Received Date"))},1,(x,y)=>let fx=(x)=>Text.Format("#[VendorID]-#[PoNum]",x&[PoNum=Text.BeforeDelimiter(Text.From(x[PoNum]),"-")]) in Value.Compare(fx(x),fx(y)))[n],each List.Accumulate(List.Skip(_),{{},_{0}{3}},(x,y)=>{x{0}&{List.FirstN(y,2)&{Date.EndOfMonth(Date.From(y{5})),x{1}-y{4}}},x{1}-y{4}}){0},(x,y)=>y))

[mddistributor1]

This looks great, but I might need a little more explaination on this to completely understand what's going on.