Power Query - Inventory allocation

[Djpejsen]

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

[Djpejsen]

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]

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 

[Syndicate_Admin]

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

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

[v-kelly-msft]

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

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

[Djpejsen]

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 

[v-kelly-msft]

Hi  @Djpejsen ,

Got it,I will mark a try,could you pls provide an editable format of the data from the 3 tables?

Best Regards,
Kelly

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

[Djpejsen]

Example file 

[v-kelly-msft · Accepted solution]

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]

Hi Kelly
The DAX and the output table C must be created in Excel and not in Power BI 
Output table C will be used as new input to a new query in Power Query Excel
Will that be possible? 

[Ehren]

It's unclear to me how Table C was constructed. Can you elaborate on what the logic is?

[Djpejsen]

Hi Ehren 
Is it still unclear to you? 

[Ehren]

Yes, I'm still lost in the overall complexity. But it sounds like Kelly may be able to help you.