Locked lesson.
About this lesson
Since there are so many joins, it really pays to explore them all. In this lesson we will look at the Left Outer, Right Outer, Full Outer and Inner joins, as well as show a trick for joining on composite keys.
Exercise files
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.
Outer and Inner Joins.xlsx25.7 KB Outer and Inner Joins - Completed.xlsx
41.9 KB
Quick reference
Outer and Inner Joins
A demonstration of merging using Outer and Inner Joins.
When to use
When you want to merge two tables of data, returning records from the first table and any matching records from the second table.
Instructions
Create staging queries for the source data tables
For each data source
- Create a new query that points to the data source
- Perform whatever transformations are required to clean up the data
- Finalize the query by choosing Home --> Close & Load To… --> Only Create Connection
Merge the staging queries
- Open the Queries & Connections pane in Excel
- Right click one of the tables you wish to merge --> Merge
- Choose the other table you wish to merge
- Select the columns to use as the merge key (i.e. the ones where the values are the same in both tables so that Power Query knows which records to match together) in both tables
- Choose the Join Kind and click OK
- Rename the newly created query
- Expand the new column(s) and choose which data to include
- Define the data types for the columns
- Finalize the query by loading it to the desired destination
Hints & tips
- A merge key that is based on multiple columns is called a concatenated key
- The order in which you select the columns for creating the merge key is important, so make sure you select them in the same order for both columns
- For non-subscription versions of Excel 2016, there is no option to select the join type when merging as only Left Outer Joins are available
- To create the other join types, first build a merge with a Left Outer Join and then manually change the merge type in the formula bar
- 00:04 In this video, we're going to look at Power Query's, outer and inner joins.
- 00:09 You'll notice we have two tables with transactions and
- 00:12 also with the chart of accounts.
- 00:14 And if we go and take a look, and expand the queries pane.
- 00:17 You'll notice that I've already got pointers set up to both of these queries
- 00:21 that do a small amount of manipulation, like naming with headers and whatnot.
- 00:26 Now, I would like to create a merge between these two tables, but
- 00:30 the challenge that I have here is that, I have multiple repeating accounts listed
- 00:35 here, like 64010, that exist in both department 150, 250, and even 350.
- 00:42 So in order to actually make this join between these things I
- 00:46 actually need to use a concatenated key to join these things,
- 00:51 because I can't look up just 64010, and I can't look just 150.
- 00:56 So you might be tempted to say, well, we could go back and modify the original
- 01:01 transaction's query to merge these two columns together with a dash.
- 01:05 That would work, but we can actually get around it without doing that as well,
- 01:09 so let's create a new merge query.
- 01:11 So I'm gonna right click on Transactions and say Merge.
- 01:15 And this will pop up my window, and it says, what would you like to merge to?
- 01:18 So we're gonna merge to the CO8 table, and I'm gonna start with the left outer join.
- 01:23 Now I can't merge as I say based on these two columns, that isn't gonna work for me.
- 01:29 But what I can do is I can actually click on Account, hold down Ctrl and
- 01:33 click Department.
- 01:35 This essentially gives me a relationship that is like 64010-150.
- 01:41 Why is that important?
- 01:43 Well, because think about a scenario where you have products 1 through 11
- 01:46 in Departments 1 through 11.
- 01:48 What is product department combination 111?
- 01:50 Is it product one, department 11, or product 11, department one.
- 01:56 So, power query, when you do this, treats it like there's delimiter in between, so
- 02:00 there's no confusion.
- 02:01 We, of course, need to do the same thing on the bottom table.
- 02:03 Hold down Ctrl and click, and it'll actually give us this thing without having
- 02:08 to make that precedent column, which is kinda nice, so, we'll now say OK.
- 02:14 And this will open up my tables for me and
- 02:16 we can now take a look at what we get with the left outer join.
- 02:20 I'm gonna expand the chart of accounts column,
- 02:23 this time I'm going to leave the original column name as prefix, reason being,
- 02:28 I have an account and a department column already.
- 02:31 So if I try and create one the same ones here, it's not gonna let me.
- 02:35 So this will actually allow me to keep these called coa.accounts,
- 02:38 coa.departments, and coa.name.
- 02:41 And when I say OK, you can see that they expand and
- 02:44 these two records 64015150 and 64010350,
- 02:49 had no matches in the other table, and therefore returned nulls.
- 02:55 But everywhere else, the data matches nicely.
- 02:59 Let's create another query, I'm gonna call this one here leftOuter,
- 03:04 just so that we know what it is.
- 03:06 And now, I'm going to expand the queries pane.
- 03:09 I could load this, and then come back and
- 03:12 create another one by merging from the Excel interface, or
- 03:16 I could just go and say, let's grab the Transactions query.
- 03:20 Reference it, we can give this new query a name,
- 03:25 like Right Outer, and I can now go and Merge Queries,
- 03:33 Against the chart of accounts using the same set up,
- 03:37 hold down Ctrl click between the two, and now change to a right outer join.
- 03:45 We'll say OK, and you'll notice in this case I end up with this funny little row
- 03:50 in the middle here called null.
- 03:52 Why is that?
- 03:53 Well if I click in the white space beside the table word you'll
- 03:57 notice that we actually have a couple of records here.
- 04:00 These are the two yellow transactions that didn't have any matching offsets
- 04:04 in the original transactions table.
- 04:06 So now when I expand these guys, of course, every single row that was null
- 04:11 inherits the information that was in that individual table.
- 04:15 So there we go, we've got these guys broken out separately, so
- 04:18 that's the right outer join, and how it looks.
- 04:21 Let's go do this again, right click on Transactions, we'll reference it.
- 04:27 This one here, we're gonna call this one the Full Outer Join.
- 04:33 Once again, we'll Merge Queries, we'll go and we'll grab the Chart of Accounts.
- 04:39 Click Account, hold down Control, and click Department.
- 04:42 Keeping holding down Control and click the Account Department down below, and now,
- 04:47 I'll choose the Full Outer Join.
- 04:49 When we say OK to this one, notice that we still have that same null row.
- 04:55 But when we expand it, we end up with nulls
- 05:00 on both sides because these two rows were the pieces that showed up originally in
- 05:05 our left outer join, that didn't have matches on the right-hand side.
- 05:09 So, notice we've got a bunch of nulls here, and
- 05:11 we got a couple of rows between seven and five that also have nulls on this side.
- 05:15 So this is giving us a complete match with all of our holes.
- 05:19 For the last one, let's go right click, we'll reference transactions again.
- 05:25 This one we will call the Inner Join, and now,
- 05:30 we're gonna merge this one as well.
- 05:34 Once again with chart of accounts, we'll hold down control,
- 05:39 and setup our linking keys, and change this to an Inner Join, and we'll say OK.
- 05:45 Now, in Excel 2016 non subscription, you can still build these.
- 05:49 You just have to create a left outer join, and then rename this last
- 05:53 piece of the joined kind enter in the formula bar, we'll make it happen.
- 05:57 This is one of the new improved Improvements that they brought us shortly
- 06:01 after the Excel 2016 went to print,
- 06:03 the subscription version obviously has all this here.
- 06:06 I'm gonna open my inner joints, and you'll notice in this one, there are no, null
- 06:10 records on the table, whatsoever because they're suppressed from both sides.
- 06:16 When I'm done what I'm gonna do now is I'm going to hit Close and Load.
- 06:19 And this is gonna load all four queries into separate tables so
- 06:23 that I can see the results of each and everyone.
- 06:28 And of course, as I add new records to the tables, I can refresh all of these queries
- 06:32 by just hitting Data Refresh All, and it will do them all at once.
Lesson notes are only available for subscribers.