How to Merge Tables with Power Query: VLOOKUP Alternative

hey there it's John from Excel campus and in this video I'm going to explain how to do a merge in power query so this would be similar to a vlookup where we create a connection between two tables but we're going to use power query for this and this is a great question submitted by Bill Evans over on our block so thank you Bill for answer asking this question here we have a table of data and this is a table of order data where we have each rows an order or a transaction and here we have a customer ID column but what we want to do is pull in the customer information and we have that information over here in this customers table so we have the customer ID here this will be the matching value between the two tables and then we have information about each customer so we want to bring this data in to our orders table and we can use power query for this and the merge so the first thing we're going to do is actually go back over to the customers table and we're going to create a connection only to this table a query connection only query so we'll select any cell inside this table we'll go to the data or power query tab and then we're going to choose from table or range that'll bring up the power query editor now if you're not familiar with power query yet I do have a separate video of an overview of power query I'll put a link to that in the description below this video so now that we have our query open all we need to do with this query is again just create that connection only so we're going to go to the home tab click the bottom half of the close and load button choose close and load - that'll bring up the import data window we're going to choose only create connection and then hit OK and that's just going to create a connection in power query to this table and now we're going to bring it in to our orders table so we'll jump back over to our order sheet again select any cell inside the orders table here do the same thing data or power query tab and then choose from table or range and I searched also mention that this data could come from a different source it does not need to come from an Excel table the CBS CSV file you could be connecting directly to a database or the or anything like that just to make this easy I've put both of these in tables but the data could come from other sources so now that we have the query editor open this is pretty simple we're going to go to the Home tab here and click merge queries and that'll bring up this merge window and you can see here at the top half of it we have our orders table and we're going to select the column that we want to do the lookup with so this is going to be the customer ID column so I'll just click that to select it and then down here at the bottom we're going to select the table that we want to do the lookup into so that'll be our orders table and again we've created the connection for that so that's why we're seeing that in our list here and from here we'll just also select the column the matching column so you can see we have the customer ID column and the ID column are the matching columns they do not need to have the same header name because we're able to select the matching columns here now down below that we have the join kind and if you're familiar with database terminology these are database terms the joins and here we're going to do a left outer join and we do get a little description here there's other options but the left outer is the default in the most common and that's going to return all the results from the first table that's what it means by all from first and the matching results from the second table and that's what we want because when we output this query to a table to use in our workbook we still want to see all of the orders here even if there's not a matching result and you can see that down here it says at the bottom the selection matches 221 of 306 rows from the table so we have some items that aren't matching and I'm going to explain how to fix that and resolve that so that's okay for now we'll just say ok for now and that's going to add this new column to our table here you can see it's called TBL customers and within each of those cells we have this table and we just click in the blank area over to the right here you can actually see the matching values here for this particular row now what we want to do is expand that out so we're going to click the expand button right here and this will give us a list of all of the columns in our customers table and we can uncheck the ID column because we don't need to bring it in twice we already have a column with the customer ID you can leave that checked if you'd like the other thing I'll uncheck is this use original column header name is prefix because that would just put the word TBL customers in front of each column name which we do not need and then we'll hit OK and that will expand out our columns so essentially we've done a vlookup and we've done a vlookup for all of these columns here all five of these columns are doing that vlookup and returning the values now this is not a vlookup but it's just similar to a vlookup in Excel that's why I'm saying it's it's kind of like a vlookup so now that we have this data here and you'll also notice we have some rows with nulls in it so this is a blank this means that power query could not find the matching value for that customer ID so if we go over here in that row that's this customer ID is 10 if we jump over here the queries pane and we can look at our customers table we only have the row ID or the customer ID up to nine so we don't have ten or anything beyond that so that's why we're having some blanks here but again that's okay we'll fix that and I'll show how to fix that so now that we have this I will go back to our orders table and we can close this pane for now and we're just going to close and load this so now I'm going to hit the top half of the split button to do a normal close and load and what that's going to do is add a new table I'm sorry new sheet to the workbook with our output table so again here's all of our orders data and if we scroll over to the right we can now see that information the customer information over here in the right hand columns so let's take a look at how to fix these blank rows like I said we didn't have all the customer information over here in our table so I do have new information down here so I'm just going to copy this I'm gonna hit ctrl C to copy that and then add it to the bottom of our table I'll just paste it right here table automatically expands to include this new information so now we have these new customers in our list and this is going to happen with your data as well over time you're going to get new lookup values whether it's customers or something else you're going to get new lookup values and you'll be maintaining this table with those values now you can also automate this step with power query but we'll leave that for a separate video depending on where your customer information is stored but for now we've updated this table here so in order to see that all we need to do is refresh our query so we'll go back over to our new sheet we're going to right click anywhere in the table here hit refresh keyboard shortcut is alt f5 that's going to very quickly rerun the query and as you can see now we have results for those customers and power query has also changed the sort order there and that's why we're seeing duplicates of that existing customer so that it can be also be controlled within the power query editor if you want to change the sort order our apply a sort order to a specific column you can do that in power query so essentially we've automated this process like I've said in all my videos with power query we're automating data cleanup processes and what I mean by that is as you get new orders regardless of where that is it could be an orders table here in the workbook or we could be connecting to a database that contains our orders as those new orders come in or if we get new customers or customer formation changes here we delete customers whatever that is as these tables and these sources are updated in order to then update our output query all we need to do is right click refresh it and then if there's any pivot tables that use this table as its source we refresh those pivot tables as well or if we have formulas those formulas are automatically calculated or recalculated as the data changes here and any summary reports pivot tables charts and dashboards that we have connected are automatically updated as well so this is the first step really in the data cleanup process now if you're not familiar with power query yet or power pivot or pivot tables macros and BB or any of these tools we have a free webinar one running right now it's called the modern Excel blueprint I'll put a link in the description below this video we can go check that out it's absolutely free to register and sign up for a time and watch at and that'll help you learn how to implement all these tools into your workflow save a ton of time with your job and become the Excel hero of the office so I hope this has helped you of course if you have any questions or suggestions please leave a comment below thanks again for watching have a great day and I'll see you in the next video [Applause]

Channel: Excel Campus - Jon

Views: 78,624

Rating: 4.9630156 out of 5

Keywords: excel, excel campus, vlookup alternative, merge queries in power query, merge tables in excel, power query, excel power query training, power query function power bi, power query explained, how to use power query, introduction to power query, power query for beginners, data automation in excel, get and transform, power query combine multiple excel files, power query vlookup, power query vlookup from another table, power query join tables, power query join two tables, microsoft

Id: 73T7isNVH_w

Length: 9min 35sec (575 seconds)

Published: Thu Jun 04 2020