Merge Queries Column and Date if Date is less than or equal to

[jilbobagins]

Howdi,

I'm hoping I can explain this well enough, I've spent most of the morning searching but to no avail.

I have 2 Tables/Queries (Details below) I want to merge the tables by the [Item] and [Dates] however I only want to merge those rows where the [Promised Date] from table 2 is less than or equal to the [Required Date] of table 2. Additionally I only want to display the 'Max' promised date that is less than or equal to (Highlighted the [running sum] total I wish to ultimately expand and show in the first table.

Hope this makes sense...

First table

I

Index	Item	Required Date	Qty Req
1	JL123	24/03/20	5
2	JL1245	30/03/20	4
3	DL4516	24/03/20	7
4	TH8898	27/03/20	6

Second Table

Index	Item	Promised Date	Sum Qty	Running Sum
1	JL123	18/03/20	4	4
2	JL123	21/03/20	5	9
3	JL123	23/03/20	2	11
4	JL123	27/03/20	4	15
5	JL1245	26/03/20	2	2
6	JL1245	04/04/20	5	7
7	JL1245	15/05/20	4	11
8	DL4516	22/03/20	5	5
9	DL4516	24/03/20	9	14

 

[Ken Puls]

Hi there,

I've knocked up something that will get you there for the merge. One caveat though, is that I haven't done the Accumulate bit in PQ, but rather as a PivotTable. The reason for this is that accumulations are super easy and efficient in PivotTables, but the accumulator functions in Power Query can be VERY slow, so aren't recommended.

Hope this helps!

 

[jilbobagins]

Thank you Ken !! I will give this a go and see how I get on :canada: