Power Query

Question

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

wdx223_daniel · Answer

= #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)=&gt;let fx=(x)=&gt;Text.Format("#[VendorID]-#[PoNum]",x&amp;[PoNum=Text.BeforeDelimiter(Text.From(x[PoNum]),"-")]) in Value.Compare(fx(x),fx(y)))[n],each List.Accumulate(List.Skip(_),{{},_{0}{3}},(x,y)=&gt;{x{0}&amp;{List.FirstN(y,2)&amp;{Date.EndOfMonth(Date.From(y{5})),x{1}-y{4}}},x{1}-y{4}}){0},(x,y)=&gt;y))
&nbsp;

cnenfrnl · Answer

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.

&nbsp;
For fun only, showcase of powerful worksheet formula,

mddistributor1 · Answer

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.&nbsp;