Forum Discussion

L70F's avatar
L70F
Icon for Helper II rankHelper II
5 years ago
Solved

Transpose PIVOT table with dynamic headings to a list

Hi, I have searched for possible solution among other posts, but unfortunataly didn't find what can help my problem.    We get price lists in pivot format where quantity values mentiones as the he...
Need Help
  • L70F's avatar
    L70F
    5 years ago

    Thank you Pijush for your link. I have watched it and solved my problem. 

    BR//Larissa

camargos88's avatar
camargos88
Icon for Community Champion rankCommunity Champion
5 years ago

L70F ,

 

You just need to use the Unpivot other columns:

 

 

Check the attached file.

 

  • L70F's avatar
    L70F
    Icon for Helper II rankHelper II
    5 years ago

    Hi, thank you for your reply, but it doesn't work to simply do unpivot other columns. Because if I would enter my second example data instead of the first one in the same table the PQ will give the wrong message that headers do not match. My target is not to create a new PQ each time I need to update input data. Target is to have a PQ that I refresh after inserting new data to the table and it updates output table automatically. 

    I hope it is more clear now. 

    BR//Larissa

    • PijushRoy's avatar
      PijushRoy
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Hi,

      Please find the step below, I run the same in EXCEL POWER QUERY, same you can run in POWER BI POWER QUERY

       

      1. Load data into power query

      2. Make sure QTY in the header (as below image)

      3. Select the first column

      4. Go to UNPIVOT COLUMN > Unpivot Other Column

      5. Done, change the Column Header as per your requirement

       

       

      Now see the result

       

      Also if you want to advance the crosstab matrix table to a simple data table, please find a tutorial for referance -myaccountingtricks.com/2020/06/convert-crosstab-matrix-excel-list-data-table-unpivot.html, may it will help you.

       

      If you find this is the solution, please mark as the solution and give kudos

       

      Thanks
      Pijush

      • L70F's avatar
        L70F
        Icon for Helper II rankHelper II
        5 years ago

        Thank you Pijush for your link. I have watched it and solved my problem. 

        BR//Larissa

    • camargos88's avatar
      camargos88
      Icon for Community Champion rankCommunity Champion
      5 years ago

      L70F ,

       

      Have you tried switching the values.

      There no reference to the headers beside "Row Labels", once you have "Row Labels" as column everything else will be unpivoted.

       

      Try it switching the values on the 2 table of the pbix provided.

      • L70F's avatar
        L70F
        Icon for Helper II rankHelper II
        5 years ago

        Hi,

        Thank you for your reply. 

        I don't work in Power BI PQ and don't know how to use your pbix file. 

        Could you please share your codes so I can use it in  Excel Power Query. Sorry for additional trouble. 

        BR//Larissa