May 2, 2014
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 🙂
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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.
Answers Post
1 Guest(s)