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

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