Forum Discussion
Power Query - Inventory allocation
Hi Experts
I have a table of Sales orders with shipments priority and requested qty. (Table A)
Every week I receive a delivery plan from our vendors. (Table B)
I have to provide a Shipment delivery plan to the Customers (Table C) and would like to create the shipment plan in Power Query
End result must be Table C (Tables A + B are input to the end result)
Hope you can support this challenge for me
Hi Djpejsen ，
Sorry for the late reply.
It is indeed not an easy case and it did take me many days.
I cant find a simpler way.Hope my way below would be a solution for you.
First go to query editor>unpivot columns to get below table:
Then in outbound table,I create several columns to tranform the data.
Finally,I got below calculated table.
Table = DISTINCT ( UNION ( SELECTCOLUMNS ( 'outbound', "Sales Order", [Sales order], "Item", 'outbound'[Item], "Proritet", 'outbound'[_proritet], "Qty", 'outbound'[final adjust], "Index", 'outbound'[Index] ), SELECTCOLUMNS ( 'outbound', "Sales Order", [Sales order], "Item", 'outbound'[Item], "Proritet", 'outbound'[new proritet], "Qty", 'outbound'[round1], "Index", 'outbound'[new index] ) ) )And you will see:
What you want is as below:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
12 Replies
It's unclear to me how Table C was constructed. Can you elaborate on what the logic is?
hi Ehren
Table C (Col Qty) is requested qty. per week
Table C (Col New Qty). Calculated allocation based on the number from Table B
If the requested qty per week exceeds the qty. in Table B, then the qty. of the last sales order must have an adjustment. And the remaining qty that cannot be covered in the requested week and must be carried over to the following week.
Eg. In Week 40 the requested qty. is total of 228 boxes, but only 200 boxes are available in the inventory. So I can send 200 boxes in week 40 and the remaining 28 boxes in week 41.
3 sales orders in WK 40
WK 40: 1015 - 82 Boxes
WK 40: 1016 - 33 Boxes
WK 40: 1008 - 85 Boxes (origin requested qty. is 113, but can only allocate 85 boxes due to insufficient inventory in WK 40. )
WK 41: 1008 - 28 Boxes
I need to allocate per sales order and per priority and per week as shown in Table C
Hi Djpejsen ,
So the table below is the one you wanna get?
If so,dax would be much easier,will you consider about using dax to realize it?
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
Yes, that will be okay as long you can provide the output below in DAX, as table C as a result.
I need a detailed overview to coordinate the detailed delivery plan with all the customers.
The pivot table you referring to is only to illustrate the difference between Tables A and C.
- Sales Order
- Item number
- Priority
- Delivery Week number
- Requested Qty
- Allocated QTY
Table A + B is the input to Table C
Hi @Djpejsen ,
So the table below is the one you wanna get?
If so,dax would be much easier,will you consider about using dax to realize it?
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
hi Ehren
If I use list.generate to do iteration over table A and together with a Running Total it should be possible to allocate the inventory to each row in Table A based on the input table B ( inventory per week)
But I have no idea how we can subtract from inventory in week sequenceE.g.
if list.generate function do iteration in Tble A from 0 and down and use Table B inventory for input . How can I do so that the list.generate knows when It must change from week 41 to 42 (Table B) as all the inventory is allocated for week 41 and list.generate must use inventory from week 42 and so on
