Locked lesson.
About this lesson
The lesson demonstrates how to use Power Query to replicate VLOOKUP's exact match functionality (without writing any VLOOKUPs!)
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.
Basic (Left Outer) Joins.xlsx25.1 KB Basic (Left Outer) Joins - Completed.xlsx
33.4 KB
Quick reference
Basic (Left Outer) Joins
A demonstration of merging using a basic Left Outer Join.
When to use
When you want to merge two tables of data, returning all 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 column to use as the merge key (i.e. the one with values that 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
- Expand the new column(s) and choose which data to include
- Define the data types for the columns
- Rename the newly created query
- Finalize the query by loading it to the desired destination
Hints & tips
- Power Query works with previews, so if your tables contain a lot of data you may not see the full list of matches
- Non-subscription versions of Excel 2016 only have the Left Outer Join option available as one of the built-in join types
- 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.