Pivoting

[Anonymous]

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 Name	Year	Sales
Account 1	2012	23408
Account 1	2013	40855
Account 1	2014	34509
Account 2	2015	23484
Account 2	2016	38243
Account 2	2017	34895
Account 2	2018	23984
Account 2	2019	43899
Account 3	2018	73854
Account 3	2019	123894

Account Name	Year	Sales	Year	Sales	Year	Sales	Year	Sales	Year	Sales	Year	Sales	Year	Sales	Year	Sales
Account 1	2012	23408	2013	40855	2014	34509	2015		2016		2017		2018		2019
Account 2	2012		2013		2014		2015	23484	2016	38243	2017	34895	2018	23984	2019	43899
Account 3	2012		2013		2014		2015		2016		2017		2018	73854	2019	123894

[Ashish_Mathur]

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]

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 Name	2016	2017	2018	2019	Sales	Revenue
Account 1	Y	Y			4569	4359
Account 2			Y	Y	34533	35434
Account 3		Y	Y	Y	43856	54646

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

[Ashish_Mathur]

Hi,

I am sorry but i really cannot understand your desired output structure.  Someone else will help you.

[BekahLoSurdo]

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