how to achieve nested subquery in PowerQuery (M Language)

[pinky]

Hi,

I struggle to find a solution in powerquery where subquery would normally be used in SQL...

There are two tables - delivery schedule - listing items, required delivery date and quantity as well as the information of the last shipment received at the site A.

And Shipments from Site B - this lists all shipments, shipping dates and shipped quantitties.

Delivery schedule show just one shipment number, the last registered at the receiving site. There can be several shipments for the same item on the same day received and the file then just shows one of them (no guarantee it is the last, ie highest shipment no.)  - hence all shipment that were shipped on the same day can be considered as "delivered".

This is where the subquery would need to be nested - I have to look-up the real shipment date for that shipment and then need to run another subquery to get all shipments for the same item on later dates... this should basically be goods in transit between the two sites.

Below is an example of the two tables and desired output

Sample

The thing is... I can't get my head around how to achiev this in M language 

Any ideas how to approach this please?

[MFelix]

Hi @pinky,

I'm refering this post to @ImkeF a great datanaut and specialist in M language.

@ImkeF can you help on this question with your expertise?

Regards

MFelix