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 headings for different columns. These differ every time we get a new price list. For further analysis with these, I need to transform PIVOT matrix table into a list format. I tried to use the PQ function unpivot, but it makes the column headings to fixed values, which makes this not work when I use a new table as input.

 

Now I use a VBA macro for this purpose, but  I want to get a solution in Power Query. 

I really appreciate your help with PQ codes, thank you. 

 

Example input1:

Row Labels12345
5367370176.65    
8701135808302.356108.95042.14281.9 
870120980  1797.61493.1 
870168680    971.25
870304080     
870304801    273

 

Example output 1:

Row LabelsQtyPrice
53673701176.65
87011358018302.35
87011358026108.9
87011358035042.1
87011358044281.9
87012098031797.6
87012098041493.1
8701686805971.25
8703048015273

 

Next time the input might be as exampe 2:

Row Labels451015202544506075
870306280      74.55   
8703158011697.85         
99137427  376.95330.75303.45     
99137455 486.15379.05343.35      
99138432  199.5153.3130.2     
99138441     58.8 44.1 39.9

 

Output example2:

Row LabelsQtyPrice
8703062804474.55
87031580141697.85
9913742710376.95
9913742715330.75
9913742720303.45
991374555486.15
9913745510379.05
9913745515343.35
9913843210199.5
9913843215153.3
9913843220130.2
991384412558.8
991384415044.1
991384417539.9
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

7 Replies

    • 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