Forum Discussion
Helper IRearranging many headers in one column
Hi there,
I'm currently capturing data from Survey Monkey for a 360 survey
The data downloaded from Survey Monkey is as follow
| Rater | Work well together | Work well together_1 | Work well together_2 | Work well together_3 | Offers Help | Note : same sequence after this... |
| Rob | Susan | Linda | Cindy | Rob | ||
| Cindy | 5 | 4 | 3 | 5 | 5 | |
| Rob | 4 | 4 | 2 | 1 | 1 |
Question : How do I rearrange the headers to get the following?
| Rater | Name | Questions | Rating |
| Cindy | Rob | Work well together | 5 |
| Cindy | Susan | Work well together | 4 |
| Cindy | Linda | Work well together | 3 |
| Cindy | Cindy | Work well together | 5 |
| Cindy | Rob | Offers help | 5 |
| Rob | Rob | Work well together | 4 |
| Rob | Susan | Work well together | 4 |
Thank you in advance!
Does not look like you unpivoted correclty.
Where does that number come from in the original dataset? (Name = Cindy, "Work well together").
It may be that you need to UNPIVOT for each column in your dataset ([Work well together], [Offers Help]... etc.)
If this is SQL, consider doing the UNPIVOT with T-SQL as it can do all columns at once (I think).
7 Replies
- ToddChitt
Super User
Looks like you would need to UNPIVOT the data. It looks like your data is currently PIVOTED, and showing summary columns. This is a Power Query operation.
If your data is in the absolute granular state, then you can use a Matrix visual to display it either way.
After un-pivoting your data, it should be in a format like this:
Aspect Name Rating
===== ==== =====
A Carol 5
A Bob 2
B Carol 4
B Bob 3
B Tim 7
ngiamThanks, Todd.
Unpivoting seems to show this. Is there anything that I should do differently?
Attribute Value Work well together Rob Work well together_1 Susan Work well together_2 Linda Work well together 5 Work well together_1 4 Work well together_2 3 - ToddChitt
Does not look like you unpivoted correclty.
Where does that number come from in the original dataset? (Name = Cindy, "Work well together").
It may be that you need to UNPIVOT for each column in your dataset ([Work well together], [Offers Help]... etc.)
If this is SQL, consider doing the UNPIVOT with T-SQL as it can do all columns at once (I think).
- ngiam
Where does that number come from in the original dataset? (Name = Cindy, "Work well together")
The original was as per below :
ater Work well together Work well together_1 Work well together_2 Work well together_3 Offers Help Note : same sequence after this... Rob Susan Linda Cindy Rob Cindy 5 4 3 5 5 Rob 4 4 2 1 1 When you say UNPIVOT each column, do you mean selecting each separate column (as outlined above) and unpivot them?
- ToddChitt
This dataset confuses me now, the more I look at it. Looks like you have TWO column headers. This may take a quite a bit of Power Query work.
Re-name "Works well together" to "Works well together\Rob" and so on for all columns. Then remove the second row by filtering it out. Then UNPIVOT each column in turn, starting with "Works well together\Rob". Result should look like this:
Rater Attribute Value
Cindy Works well together\Rob 5
Cindy Works well togehter_1\Susan 4
Next, do a Find and Replace of "_1" in Attribute, replace with empty string.
Finally, SPLIT the Attribute column on the "\"
Hi ngiam ,
Here are the steps you can follow：
1. Power Query - Select all columns except [Rater] - Transform - Unpivot Columns.
Result：
2. Create calculated table.
Table 2 = FILTER('Table','Table'[Rater]<>BLANK())3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- ngiam
Thank you! this works!
