Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am not sure if this is the appropriate title for my question but ulitmately what I want to do is take a table as such:
Survey_Key | Q1 | Q2 | Q3 |
A | 4 | 2 | 5 |
B | 3 | 3 | 5 |
and turn it into this:
Survey_Key | Question | Answer |
A | Q1 | 4 |
A | Q2 | 2 |
A | Q3 | 5 |
B | Q1 | 3 |
B | Q2 | 3 |
B | Q3 | 5 |
The only way I can think of right now is to split the query up into multiple queries with only the Survey_Key and one question column each, create columns for the question number and answer, and append them together. I have 10 or so questions so I would like to avoid that as I imagine there has to be a more efficient way.
Solved! Go to Solution.
Hi @shep123,
In the Query Editor, do the following.
Select Survey_Key column and right-click on it.
Select Unpivot Other Columns. This should place the column headers in the newly created column Attribute and the row in Values column.
Note: if your Q columns have null values, replace them with something else link "blankvalue" prior to unpivoting your column as the null rows get deleted with unpivoting. Once you're done unpivoting, replace "blankvalue" with null.
Proud to be a Super User!
Hi @shep123,
In the Query Editor, do the following.
Select Survey_Key column and right-click on it.
Select Unpivot Other Columns. This should place the column headers in the newly created column Attribute and the row in Values column.
Note: if your Q columns have null values, replace them with something else link "blankvalue" prior to unpivoting your column as the null rows get deleted with unpivoting. Once you're done unpivoting, replace "blankvalue" with null.
Proud to be a Super User!
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |