Transposing data for better analysis

[gckcmc]

OK Excel junkies, I have been handed a large file of survey data.  I am trying to simplify the data to make my import into PBI easier, and my visualizations much more flexible.

I have data columns that look like this:

NAME      LOCATION     Group1:Comms     Group1:Flexibility        Group2:Comms    Group2:Flexibility

Susie         Jamaica                               5                               4                               4                             4.5

...

I have a couple of hundred columns that are survey results in categories based on the group "Susie" is rating.  I'm thinking the better way to handle this data is to change it to look like this:

NAME      LOCATION     Group#  Comms    Flexibility 

Susie         Jamaica         Group1            5                 4

Susie         Jamaica         Group2            4              4.5

I've seen Excel transforms on columns to rows etc...but this is effectively duplicating rows and adding in the new information as a 2nd row.  

I am fairly versed in PowerQuery as well, so I could leave the data and do the transform inside that if someone has a great idea on how to solve that...

thanks!

[#2 · Accepted solution]

@gckcmc 

Hello, all you need is Power Query to Unpivot  column

You can watch the video below to see exactly how to unpivot using Power Query.

https://www.youtube.com/watch?v=JMrfbv2h7p8

If this answers suits you, kindly do accept as the Best Response

Thanks

[#3]

@Abiola1 

Perfect, thanks!

[#4]

Glad to help