hi All
I am hoping I can do what I want to do in PQ. I wanted to attach a file but was told I didn't have permission ?
In the Movie Types sheet I've got a list of movies that have been assigned a number of different categories (no resemblance to reality AT ALL). In the second sheet (Responses) I've got a list of responses to questions. I want to end up with what I have in the Desired Result sheet...how can I do that in PQ?
Movie Types
| Movie Name | Movie Type |
| The Revenant | Thriller |
| The Revenant | Historical |
| The Revenant | TV Series |
| Three Stooges | Documentary |
| Three Stooges | Historical |
| Three Stooges | Thriller |
| Godfather Part 1 | Romance |
| Godfather Part 1 | Gangster |
| Godfather Part 1 | Historical |
Responses
| Respondent 1 | Favourite film | Score (10) |
| Larry | The Revenant | 5 |
| Curley | Three Stooges | 3 |
| Mo | Godfather Part 1 | 8 |
Desired Results
| Respondent 1 | Favourite film | Score (10) | Movie Type |
| Larry | The Revenant | 5 | Thriller |
| Larry | The Revenant | 5 | Historical |
| Larry | The Revenant | 5 | TV Series |
| Curley | Three Stooges | 3 | Documentary |
| Curley | Three Stooges | 3 | Historical |
| Curley | Three Stooges | 3 | Thriller |
| Mo | Godfather Part 1 | 8 | Romance |
| Mo | Godfather Part 1 | 8 | Gangster |
| Mo | Godfather Part 1 | 8 | Historical |
Thank you 🙂
It's not hard 🙂
First, load those 2 tables with movies and responses.
You have 2 choices: to create a new query, and merge the previous 2 queries, using the Movie Name as the Key, with the command "Merge Queries As New" from Power Query ribbon, Home Tab.
Use the Left Outer Join Type.
Expand those 2 columns you're interested in, and that's all, it will display exactly as you wanted. This will create a third query, with the merged table.
The other option is to use the command "Merge Queries", added as a step in the Movies Query, the rest of the steps are the same as described.
Hi Catalin
Thanks for that. I actually went back to it and had figured that out and was just coming back to say that I had the answer! Thank you so much for your reply though 🙂
