Forum Discussion
Unpivot table
I have a quiz result in the following format
| Name | id | Q1 | 1 | Q2 | 1 |
| Staff A | 102 | TRUE | 1 | FALSE | 0 |
| Staff B | 222 | FALSE | 0 | FALSE | 0 |
| Staff C | 312 | TRUE | 1 | TRUE | 1 |
How do I unpivot the table to this format ?
| Name | id | Question | Ans | Score |
| Staff A | 102 | Q1 | TRUE | 1 |
| Staff A | 102 | Q2 | FALSE | 0 |
| Staff B | 222 | Q1 | FALSE | 0 |
| Staff B | 222 | Q2 | FALSE | 0 |
| Staff C | 312 | Q1 | TRUE | 1 |
| Staff C | 312 | Q2 | TRUE | 1 |
4 Replies
The columns with 0 and 1 in are redundant so you could remove those.
Then Select the first 2 columns and Unpivot others.
If you want, you can add a custom column to represent TRUE/FALSE as 1/0
The true/false has no relation to the 1/0. It just happened that for these questions the true answer is the correct answer
In Power Query, highlight the Name ID column and choose unpivot other columns
My example was not very clear. The '1' label is the maximum score possible for the question and true or false answer does not equal to 1 or 0 score
Input
Name id Q1 Max Score Q2 Max Score Staff A 102 A 1 C 0 Staff B 222 C 0 B 1 Staff C 312 B 0 A 0 The desired output
Name id Question Ans Score Staff A 102 Q1 A 1 Staff A 102 Q2 C 0 Staff B 222 Q1 C 0 Staff B 222 Q2 B 1 Staff C 312 Q1 B 0 Staff C 312 Q2 A 1
