cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

1 ACCEPTED SOLUTION
Community Support

Hi @Djpejsen ，

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],
"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

12 REPLIES 12
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

Frequent Visitor

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?

If so,dax would be much easier,will you consider about using dax to realize it?

Best Regards,
Kelly

Community Support

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

Frequent Visitor

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

Community Support

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

Frequent Visitor
Community Support

Hi @Djpejsen ，

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],
"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

Frequent Visitor

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?

Microsoft Employee

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

Frequent Visitor

Hi Ehren
Is it still unclear to you?

Microsoft Employee

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors