Better Than Vlookup. How to Merge or Join Data w/ Power Query Excel 2016 (Get & Transform) – Part 2


so I’m here in Excel 2016 and to use
power query we can come over to the data tab where it’s been renamed to get and
transform now if you’re using Excel 2010 or Excel 2013 and you have the add in
installed you’re gonna have a different tab up the top here called power query
so instead of data you actually go to a power query tab but here in Excel 2016
I go to data get data and you can see here that you have a whole range of
different data sources for this example where we’re going to merge a couple of
different tables together I’ve got an example set up in a simple text file so
I’m gonna come over to text file and I have a folder sitting here on my desktop
called merging data and I’m first gonna load in this table employees you can see
that you get a preview of your data here so you have a few buttons here we got a
load button and an edit button to edit the data before we bring it into Excel
the load button has a little drop-down so we can either load or load to load
automatically loads our data into a new worksheet load to gives us a few additional
options so we can see up the top here that our first option is table so we
have a couple of other options here pivot table report and pivot chart these
basically allow you to use your data set in a pivot table without actually having
to see it inside a sheet now the benefit of this is that your file sizes will
actually be much smaller and your calculations will actually run faster as
well so there is actually a benefit to that if you don’t actually need to bring
all of your data into a sheet inside of Excel this option over here only create
connection allows you to create a link to your data so you can refresh that and
then use that connection to pull the data into different places within Excel we have an option down here to add our data to a
new worksheet this little option down the bottom here add this data to the
data model is actually adding your data to the PowerPivot data model
PowerPivot was an add-in which basically allowed you to process a large datasets
I’ll leave a link in the lecture resources so that you can see if you
have the right copy of Excel to be able to use it up here we have where we want
to put our data so we have here a new worksheet and on top of this we have an
option for existing worksheet so for this example I’m going to drop all my
tables into this one worksheet here just so that we can see them easily for
illustrative purposes only it’s generally not the best idea to add
multiple datasets to the same sheet but these are very small datasets and it’s
just easier to demonstrate it so I’m going to drop this into our existing
worksheet into cell a1 I’m gonna click OK our data loads up into this green table
over here and we noticed that we have a query and connection over the side here
called employees I’m going to go through this
same process again for my second table so data get data from file from text and
my second table over here is employee data again a very simple table which I’m
going to click load to and I’m going to drop it into the existing worksheet and
I’m going to stick it right here click OK
so here you can see we have two tables of data and what we want to do is merge
these two together so you notice here that our second table has a couple of
extra columns of data if we wanted to add both of these columns to our first
table with a vlookup you’d have to write two different vlookup formulas or
perhaps a vlookup formula with some sort of match formula within it to look up
the column name to do this instead with power query what we’re going to do is we
gonna come over to data again get data combine queries and we’re gonna do this
merge so here we have this screen where we can select our two tables the first
one I have the top here is employees and the second table is going to be employee
data what we need to do here is we need to select the column that these two
tables are going to join on so this is like the first column that you would
specify for a vlookup so here I’m going to select employee ID here and employee
ID here down here we can see that it’s telling us the selection has matched two
out of the three rows so we can see we have employee IDs one two and three here
and one two and four here so one and two are matched against both tables and
three and four aren’t matching down here we have something which is a little
bit new if you’ve only used vlookups before but very familiar if you have
used databases which is the join kind so here we’ve got a left outer join also
known as just a left join if we click this drop down we can see we have a
whole range of options here from right outer join full outer join inner left
anti and right anti so I’m going to leave this on left which is roughly
equivalent of a vlookup and I’m going to click OK we’ll come to this screen here
which is the query editor and here on our second table employee data I can
expand this to select the columns that I want to add to the data set so here it
selected all of the columns for me I’m gonna deselect employee ID since I don’t
need that to be shown on here twice and I’m going to click OK so here we can see
we have our extra fields added on to our data set and I’m going to load and close
these two again we have load or load to so load to gives you a few additional
options like we had before and I’m going to drop this on to the
existing sheet over here so we can see what it looks like now the nice thing
about power query is that on top of not having to write any formulas this
actually works really fast on larger data sets and whenever our data gets
updated all we have to do is click the refresh button
and as long as the files are in the same location with these same file names then
these will automatically refresh everything in your sheet now if your
source files have changed name or changed locations you can also update those as
well by coming into the query editor for any one of these queries and we can see
here that there’s a number of steps the first step is called source and we have
this little cog here which allows us to adjust the settings so here we can
basically browse for a new file if that file has changed location let’s cancel
out of this now I want to show you one of the types of problems that a join
solves really well which is generally pretty hard to do with a vlookup formula
in this example here we only have one record per employee in our second table
but what happens if we had multiple records so for example for one of our
employees let’s say we want to pull up their history and see what department
they were in for the last three years so I have a data set
to illustrate this so let me come back to data and get from file and I’m gonna
load up this file department history now we can see here that in this data set we
only have one employee ID but it’s duplicated three times to show three
different records for this one employee so they’ve changed from 2011 to 2013
they’ve gone through three different departments I’m gonna load up this data
and I’m gonna load it to and they’re gonna just drop it on our same sheet
again here again so normally if you were writing a
vlookup formula to pull on this data on to our original table up the top here
you would only pull back the first record if you wanted to pull the other
records that’s relatively difficult however with power query using a left
join that’s actually really straightforward and it is actually the
default behavior so let’s quickly take a look at that now again I’m gonna do the
same operation as I did before I’m gonna come to data get data combine merge so
my first table I’m going to set as employees and the second table I’m going
to set as department history again I’m going to select the employee ID as the
field that I’m going to join on and I’m gonna keep this as a left join and click
OK let’s expand this to add the department
and year then you click OK and we can see that these fields have been tacked
on like so notice that the original data set has actually grown in size now which
is not something that you can do with formulas in Excel since all of the
formulas are linked to a fixed range of cells I’m going to close and load this
to our sheet let’s drop it over here and that is merging data with power query
in Excel

6 thoughts on “Better Than Vlookup. How to Merge or Join Data w/ Power Query Excel 2016 (Get & Transform) – Part 2

  1. YEAH! The VLOOKUP vs. INDEX/MATCH conversation needs to die because Power Query brings up merges.

  2. When you created the left join for the first two tables you imported, why did it only bring in employee 1001, 1002, and 1003 and not employee 1004 that is on the second table in column E, row 4? Was it because its functioning similar to a vlookup and since there is no 1004 in the first table in column a, top left corner, then it won't create a row for 1004?

  3. I think this is exactly what I was looking for.

    I have 2 tables. 1 table has all the sales data I need, and the other table has a “qualified” column that determines if they’re qualified to receive commission

    Here’s the catch though. The same employee, with same employee id could be eligible for commission in 1 month, and not eligible in the other.

    I’m hoping it differentiates and doesn’t just pull the first “qualified” result for each time Emp ID 98637 shows up in sales data table. Hoping it will find when that emp is eligible and when it’s not.

Leave a Reply

Your email address will not be published. Required fields are marked *