Rearranging survey data to from columns to rows

[Neil_Cooper]

Hi Everyone

I wonder if you can help?  I have survey data I am trying to organise into a table showing the top 3 areas and the bottom 3 areas (based on average scores for each question) in separate tables.  Whilst this in theory sounds easy enough I am finding that as these are on separate columns in the example below I am finding this doesnt work in Power BI for two reasons.  Firstly it brings the ratings into the rows and secondly the top N cannot be used as these are all separate columns.

What I need is this:

I wonder if there is a way of reorganising the data in Power BI to achieve this.  

Hope this makes sense and many thanks in advance 🙂

Best wishes

Neil 

[mahoneypat · Accepted solution]

In the query editor, highlight your first two columns, and right click and choose Unpivot Other Columns.  Load that table, and you can make a table or matrix to get your result with the Attribute column (or whatever you rename it to) and the average of the Value column.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Neil_Cooper]

Thank you so much!  This is genius 😊  I would have not figured this out on my own so thank you so much.  It's so simple and did just the trick!  Really appreciate it, your a lifesaver!

[amitchandak]

@Neil_Cooper , Not very clear, You can unpivot

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Neil_Cooper]

My apologies if this wasn't clear it is a tricky one to explain.  Really appreciate you sending the response over.  I was able to use the unpivot other columns and then use the attritubes to get the results I needed.

Best wishes

Neil