Oz du Soleil demonstrates how to merge two data sets using multiple criteria.
- [Instructor] Next we're going to look at how to merge data setswhen we have to match two fields. So here's the deal we are hosting the Carpinteria Conference. And we've got these two data sets, one listing the instructors and their sessions, another listing the rooms and the session that those rooms are being occupied. Now to help our attendees we would like to have a master list so that people know Thursday morning Ken is teaching in the gold room.
Since we want to create this master list that tells us that we need to do a full outer join. The twist here is that we need to match both the day and the session. And here's how we do it. Our data sets are already in tables now we have to make the queries that we're going to merge. The cursor is already in the instructor's data set. We go to Data, From Table. Close and load as a connection only because we haven't done anything to this data, to load it to the workbook would be the same data we already have.
Close and load to, only create connection, load. And we see on the right side that the instructor's query is loaded as a connection only. Let's make the rooms query. With the cursor in the data set we go to data in the ribbon and we go to From Table. There's our query, load it as a connection only. We've got our queries, now we can do our full outer join.
New query, combine queries, merge. We want to select the full outer join. Full outer. Up top we're going to select the instructors, the bottom rooms. Next we select the fields that need to be matched, day and day. In order to select the session we have to hold down the control key. Now we select session and session.
And notice the one next to day and the two next to session confirming that we are going to match this up by two fields. Select okay. Our instructor data is expanded, let's expand the room data. We don't want to use original column name as prefix, okay. Notice in row one we have Thursday morning Ken, Thursday morning Gold. We don't need the day one and session one columns, let's get rid of them.
Highlight day, right click remove. Right click, remove. Let's close and load this to the workbook. Here's our data, let's look at it closer. And this is exactly what we wanted for our attendees. It is now clear Tuesday morning, Gaston is teaching in the Emerald room, and we did that with a full outer join matching two criteria.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins