This is an oversimplification of the dataset I need to work with. Actually, I have to process over 60k records spanning over 27 questions. The data is downloaded through an API and every day new entries can be added. On top of that, the answers are not 1 -> 5 but are textual lickert scales that sometimes have 5 answers, sometimes have 3 answers and sometimes even have 4 answers. The text can vary, even between two 5 answers-scales.
Anyway: the data is deliverd to me in this format, but I must give it back to my researchers (not data-analsts, but doctoral researchers) in the format below: each respondent has one line with all answers to the questions on one line.
I want to use PowerBi for this for two reasons:
- to familiarize myself with this program
- because my department has similar questionnaire data it wants to analyze and visualize
I've been trying for days now to find the correct pivot, but I can't find it 😕
Can someone help me in grouping/pivoting my data the correct way?
SOURCE DATA
I don't know how to upload attachments, so here is a pastebin
Respondent Location Question Answer
1 Disney World What City are you from? New York
1 Disney World Rate: Captain America (scale 1-5) 1
1 Disney World Rate: Iron Man (scale 1-5) 2
1 Disney World Rate: Scarlet Witch (scale 1-5) 5
1 Disney World Rate: Hulka (scale 1-5) 1
1 Disney World Rate: the food in the park (scale 1-3) 2
1 Disney World Rate: the price of the park (scale 1-3) 3
2 Disney World What City are you from? LA
2 Disney World Rate: Captain America (scale 1-5) 3
2 Disney World Rate: Iron Man (scale 1-5) 3
2 Disney World Rate: Scarlet Witch (scale 1-5) 3
2 Disney World Rate: Hulka (scale 1-5) 3
2 Disney World Rate: the food in the park (scale 1-3) 3
2 Disney World Rate: the price of the park (scale 1-3) 3
3 Disney Land What City are you from? New York
3 Disney Land Rate: Captain America (scale 1-5) 5
3 Disney Land Rate: Iron Man (scale 1-5) 4
3 Disney Land Rate: Scarlet Witch (scale 1-5) 1
3 Disney Land Rate: Hulka (scale 1-5) 4
3 Disney Land Rate: the food in the park (scale 1-3) 2
3 Disney Land Rate: the price of the park (scale 1-3) 1
FORMAT I NEED
(I renamed the questions to Q1, but in PowerBi, the columns can have the full questions as header)
Respondent Location Q1 Q2 Q3 Q4 Q5 Q6 Q7
1 Disney World New York 1 2 5 1 2 3
2 Disney World LA 3 3 3 3 3 3
3 Disney Land New York 5 4 1 5 2 1
FORMAT I WRONGLY GET
because I can't seem to find the correct pivot or grouping..
Respondent Location Q1 Q2 Q3 Q4 Q5 Q6 Q7
1 Disney World New York
1 Disney World 1
1 Disney World 2
1 Disney World 5
1 Disney World 1
1 Disney World 2
1 Disney World 3
...
Thank you for helping me on my way!
