Forum Discussion

Djpejsen's avatar
Djpejsen
Frequent Visitor
5 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
    5 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!

     

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
Djpejsen
Frequent Visitor
5 years ago

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's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      Djpejsen
      Frequent Visitor
      5 years ago

      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? 

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 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!