Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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).
Proud to be a Super User! | |
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
Thank you! this works!
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 "\"
Proud to be a Super User! | |
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).
Proud to be a Super User! | |
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?
Thanks, 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 |
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
Proud to be a Super User! | |
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |