PowerBI: Pivot questionnaire data that's returned record by record into one line per respondent

Question

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 use PowerBi for this for two reasons:

1. to familiarize myself with this program
2. 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

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?                 Los Angeles
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    Los Angeles 3   3   3   3   3   3
3           Disney Land     New York    5   4   1   5   2   1

FORMAT I WRONGLY GET

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
...