gery
New member
- Joined
- Oct 11, 2020
- Messages
- 1
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Office 365
Hello there,
I need to calculate the following - I have available products with expiration date ("Sell By" column) - let's call them batches and orders - which should be fulfilled on a certain date. So 1. I have to check if the dates matches (I have already done this) and 2. from the possible matches I have to determine how many products I can use from each batch and if I will have any product left from the batches.
So - the first two small tables is the initial data, the third bigger table - I have merged them (here, if the date of the batch is older then the order date, the "available product" will be 0) and the last two columns are the results I am looking for.
It seems the easiest way to solve this is to use two nested for-loops - but I can't wrap my head on how to simulate this in PQ. I have found list.generate function but I am not sure how exactly to do this with it. Of course, any other suggestion is welcomed - I have tried several different ways, but it always comes to the fact that the number of the orders and the batches can change with time - and the solution should not depend on the exact number (rather to be dynamic).
View attachment NestedLoops ExcelGuru.xlsx
Thanks a lot for any help on this!
Gery
I need to calculate the following - I have available products with expiration date ("Sell By" column) - let's call them batches and orders - which should be fulfilled on a certain date. So 1. I have to check if the dates matches (I have already done this) and 2. from the possible matches I have to determine how many products I can use from each batch and if I will have any product left from the batches.
So - the first two small tables is the initial data, the third bigger table - I have merged them (here, if the date of the batch is older then the order date, the "available product" will be 0) and the last two columns are the results I am looking for.
It seems the easiest way to solve this is to use two nested for-loops - but I can't wrap my head on how to simulate this in PQ. I have found list.generate function but I am not sure how exactly to do this with it. Of course, any other suggestion is welcomed - I have tried several different ways, but it always comes to the fact that the number of the orders and the batches can change with time - and the solution should not depend on the exact number (rather to be dynamic).
View attachment NestedLoops ExcelGuru.xlsx
Thanks a lot for any help on this!
Gery