Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Pivoting

Hi, I am in need of converting the data from rows to columns. Example is provided below. Not too sure pivoting feature will help achieve this but my efforts with it weren't fruitful. Appreciate your help.

 

Account NameYearSales
Account 1201223408
Account 1201340855
Account 1201434509
Account 2201523484
Account 2201638243
Account 2201734895
Account 2201823984
Account 2201943899
Account 3201873854
Account 32019123894

 

Account NameYearSalesYearSalesYearSalesYearSalesYearSalesYearSalesYearSalesYearSales
Account 12012234082013408552014345092015 2016 2017 2018 2019 
Account 22012 2013 2014 201523484201638243201734895201823984201943899
Account 32012 2013 2014 2015 2016 2017 2018738542019123894
Need Help

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    Why would you want a structure like this?  Why not have actual years as column labels and Sales of that year for that Account at the intersection.  I do not see any good use of your desired output structure.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I actually want to plot this data on a matrix visual, Account Name on Rows; Years on column and Sales (plus many more fields) on the values. I have a measure, which I will put on values for the Year.

       

      Something like this (I didn't try to refer the data I used in my initial thread for this post, sorry):

      Account Name2016201720182019SalesRevenue
      Account 1YY  45694359
      Account 2  YY3453335434
      Account 3 YYY4385654646

       

      I am not sure how to do this mix and match i.e. some fields going on columns well and some on values well. Maybe there is a custom visual, which does this kind of report.

       

      Thanks again for your prompt help.

      -SN

      • BekahLoSurdo's avatar
        BekahLoSurdo
        Resolver IV
        7 years ago

        Hi Anonymous,

        In terms of wondering if there is a better visualization for what you are looking for, someone with more experience than me may have another idea.

         

        However, to answer your first post (creating columns from rows with a dynamic quantity of columns that will change with the data), I did something similiar here:

        https://community.powerbi.com/t5/Desktop/Reorder-rows/m-p/693066#M334243

         

        Let me know if you have questions about adjusting the code to fit your needs; the underlying concept is the same but your column header requirements are slightly different. 

         

        Best,

        Bekah