Outer and Inner Joins

Quick reference

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