cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Djpejsen
Frequent Visitor

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
Allocation.png

1 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:

vkellymsft_0-1634029587210.png

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:

vkellymsft_1-1634029809958.png

What you want is as below:

vkellymsft_2-1634029838374.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

12 REPLIES 12
Djpejsen
Frequent Visitor

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

Allocation.png




 

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 

 

Hi  @Djpejsen ,

 

So the table below is the one you wanna get?

vkellymsft_0-1633512880893.png

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!

Hi  @Djpejsen ,

 

So the table below is the one you wanna get?

vkellymsft_0-1633512880893.png

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!

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 ,

 

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!

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:

vkellymsft_0-1634029587210.png

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:

vkellymsft_1-1634029809958.png

What you want is as below:

vkellymsft_2-1634029838374.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

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
Microsoft
Microsoft

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

Djpejsen
Frequent Visitor

Hi Ehren 
Is it still unclear to you? 

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors