Forum Discussion

Djpejsen's avatar
Djpejsen
Frequent Visitor
4 years ago
Solved

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 deliver...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    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,
    Kelly

    Did I answer your question? Mark my reply as a solution!

     

Djpejsen's avatar
Djpejsen
Frequent Visitor
4 years ago

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






 

Djpejsen's avatar
Djpejsen
Frequent Visitor
4 years ago

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 sequence 

E.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