Basic (Left Outer) Joins

Quick reference

• 00:05 In this video we're gonna look at basic left outer or one to many joins.
• 00:11 Now you'll notice here that we have an inventory table on the left-hand side in
• 00:14 yellow and we have a sales transactions table on the right-hand side in blue.
• 00:20 The key thing that's really important around this is that the SKU number, or
• 00:24 stock keeping unit, in this table of inventory items is unique,
• 00:29 there's only one of any instance.
• 00:32 But naturally, when we actually get over to the sales transactions table,
• 00:36 we tried to sell the same SKU many, many, many times.
• 00:40 So this is what we call a one to many relationship.
• 00:43 What we can see inside our sales table is that we have dates,
• 00:48 skills number plans, and sales quantities, but we don't have any prices, so we can't
• 00:52 tell how much money we actually made, and that's a little bit of a challenge.
• 00:56 So, the way we would solve this, using Excel normally,
• 00:59 is we would create a V lookup statement over here, and
• 01:03 look up the SKU number in the table on the left to return the sales price, or
• 01:08 the margin, or any of the other columns that we want, but
• 01:11 we'd have to do each one individually, which is kind of a bit of a pain.
• 01:16 So let's look at this the Power Query way.
• 01:17 The first thing that we need is we need a connector to be able to work with.
• 01:23 So we're gonna go and click one of the cells inside our yellow table, and
• 01:28 we'll create a new query from table arrange.
• 01:32 Now, we're not gonna do a lot inside here.
• 01:34 As a matter of fact, we're not gonna do anything inside here.
• 01:37 This is our inventory table.
• 01:39 This is the pointer we need to the data.
• 01:41 So we're now gonna go and say close and load to,
• 01:43 and we're just gonna load this to a connection only.
• 01:47 Just so that Power Query can actually see it.
• 01:50 We'll also do the same thing with the sales transactions table
• 01:54 we're gonna go grab from table arrange.
• 01:57 The only change we're gonna make in here is to force this to be a date.
• 02:02 At this point, we'll replace our column type and now go close and load two.
• 02:09 And once again, there's no point in landing it and duplicating the data, so
• 02:13 we'll just say only create connection.
• 02:16 Now that Power Query can see both of these tables This is perfect.
• 02:21 What we're gonna do is we're gonna start with the sales table,
• 02:25 I'm gonna right click it, and I'm gonna say Merge, and
• 02:29 at this point it will create a new query for me.
• 02:32 And it says, all right, you wanna merge Sales with what?
• 02:35 Notice that I can merge it against itself, there's no point in doing that here, but
• 02:39 it's nice to know that we can, or I can merge it against the inventory table.
• 02:44 Now, at this point, you'll notice that the OK button is not lit up, and
• 02:48 that's because I haven't told it what would you essentially like to VLOOKUP on.
• 02:54 Well, the common key here that we want to look for
• 02:57 is the SKU number, where we have a one to many relationship.
• 03:00 Now you'll notice that it tells me it's matched the first 20 out of the first
• 03:03 20 rows.
• 03:05 Because Power Query works with previews, there is very much a possibility that you
• 03:09 may not see a one to one message in this case.
• 03:12 You might see that you have matched 50 out of the first 5000 rows or something
• 03:16 like that, this is because Power Query uses previous from both of the tables.
• 03:21 If you have a lot of data in both,
• 03:23 it's very likely that you won't be able to see all of the matches right off the bat.
• 03:28 If you do see something where you've matched 1 out of the first 10,000 rows,
• 03:31 that could be okay.
• 03:33 If it's 0 out of the first 100,000 rows,
• 03:35 you probably chose a poor column to match on.
• 03:38 The other thing that you'll notice is that we have the left outer join,
• 03:41 which is what we gonna focus on here.
• 03:43 There are other join types that are available, although unfortunately,
• 03:47 these don't show in the non-subscription version of Excel 2016.
• 03:51 So again, if you don't have those, might be time for
• 03:53 upgrade, although you can still make them happen, but for
• 03:56 right now, we're gonna stick with the left outer join.
• 03:59 We'll say okay.
• 04:01 This is gonna pull in a new query called merge one that merges two together.
• 04:05 I'm gonna go and give this guy here a name like Full,
• 04:08 just because it's gonna be a full listing here.
• 04:11 And at this point, you'll notice that it's got all of the original items
• 04:16 from the transactions table, the cells, and
• 04:19 now, if I expand inventory, I can go and say, all right.
• 04:25 Let's not use the column name as prefix.
• 04:29 I don't really need to bring over the SKU's since I already have it,
• 04:31 and the brand I already have, and maybe Well, I don't know.
• 04:35 You know what? Let's bring the rest of them over.
• 04:37 We can say OK.
• 04:39 And just like that I have performed a huge amount of VLOOKUPs all in one step.
• 04:43 If I want to see the sales quantity times the sales price,
• 04:47 I can hold down my Control key, grab the sales price column,
• 04:51 go to Add Column Standard, and I can multiply the two things together
• 04:57 to give myself a nice new column that I can call Gross Revenue.
• 05:03 And just like that, I've pulled the two tables in, I've made some changes,
• 05:07 I've merged them together, and I can land these guys out in to a nice worksheet.
• 05:12 There we go. And the the beauty here is that as new
• 05:15 items are added to both tables, I can just hit a data, refresh all,
• 05:19 and it'll refresh this query for me regularly.

Lesson notes are only available for subscribers.