Excel Power Query with Easy VLOOKUP During Import - Episode 2292

Microsoft Excel Tutorial: Cleaning Data with Excel Power Query and doing a VLOOKUP on the way in.

Welcome to another exciting episode of the MrExcel podcast! I'm Bill Jelen and I'm here at Microsoft Ignite 2019, where I have the privilege of recording this episode in one of the eight podcast booths provided by Microsoft. They've given me an hour, but I know I can record more than just one podcast, so let's dive right in.

In this episode, we'll be discussing how to use Power Query to perform a VLOOKUP during the import process. This is one of the many podcasts I'll be recording at Ignite 2019, and I want to thank you for tuning in.

I had to apply for this booth and promised to demonstrate how to use Power Query for a VLOOKUP during import. So, without further ado, let's get started. I have a CSV file with dates, items, quantities, and regions, but it's missing important information like descriptions, manufacturers, and list prices. Luckily, I have a small table with this information that I need to merge with the CSV file during import.

To do this, we'll first need to turn the table into a proper Excel table by selecting it and clicking "Insert" and then "Table" or using the shortcut "Ctrl + T". We'll name this table "PartLookup" and then go to the "Data" tab and select "From Table/Range". This will open up Power Query, which is built into Excel 2016 and available as an add-in for 2013 and 2010. We'll then select our CSV file and click "Close and Load To..." to create a connection.

Next, we'll need to get the CSV file into Power Query by going to "Data" and selecting "From Text/CSV". We'll then select our file and click "Transform Data". This will open up the Power Query Editor, where we can see a preview of our data. We don't need to make any changes, so we'll just click "Close and Load" and name the query "Sales CSV file". Again, we'll choose to create a connection instead of loading the data into the spreadsheet.

Now comes the important part - merging the two tables together. We'll go to "Data", select "Get Data", and then "Combine Queries". Here, we'll choose "Merge" and select our first table (Sales CSV file) and our second table (PartLookup). We'll then need to match the "Item" column from the first table with the "Part Number" column from the second table. We'll also select a left outer join, which means we'll keep all the rows from the first table and only add matching rows from the second table.

Once we click "OK", we'll see a new column called "PartLookup" with a small icon next to it. We'll click on this icon and uncheck the box next to "Part Number" to remove it from our final table. We'll then click "OK" and see our merged table with all the necessary information. We can rearrange the columns as needed and then click "Close and Load" to bring the data back into our spreadsheet.

And there you have it - a simple and efficient way to perform a VLOOKUP during the import process using Power Query. Don't forget to subscribe to our channel and ring the bell for notifications. And if you're at Ignite 2019, be sure to enter Microsoft's podcast sweepstakes for a chance to win a pair of Microsoft Surface earbuds. Thanks for tuning in and we'll see you next time for another episode of the MrExcel podcast.

Microsoft Excel has an awesome tool to clean data called Power Query.
This video shows you how to import data to Excel and do a VLOOKUP during import.

Recorded Live at Ignite 2019 in Orlando: How to import a file using Excel Power Query while doing a VLOOKUP during import.

#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#powerquery

This video answers these common search terms:
power query adding a vlookup table during import
doing a vlookup while loading data in excel power query
joining tables in excel power query
power query import with vlookup in excel
excel power query vlookup during data import
how to use vlookup in power query
excel power query with external vlookup table
power query import data with vlookup reference

Table of Contents
(0:00) Clicking Like really helps the algorithm
(0:20) Power Query with VLOOKUP on the way in
(0:59) Ctrl+T to make into a table
(1:24) Connect to the lookup table
(2:02) Load CSV file into Power Query
(2:57) Merge Query in Power Query is like VLOOKUP
(3:32) Merge dialog in Power Query is too subtle
(4:04) Expand lookup table to get Totals
(4:37) Load results to Excel grid
(4:53) Refreshing Power Query when you get new data
(5:13) Clicking Like really helps the algorithm

[peggymahabir]

Thank you, apart from learning, I really enjoyed the way you spoke in an exciting way. It made me want to listen. Keep bringing content. Much appreciated as I am new to power query but loving it.

[paulcarter]

Thanks Bill, I've learned most of what I Know about Excel from you and Mike, another issue solved 👍👍🙂🙂

[excelisfun]

Even better the second time with the visuals at the beginning : )

[titan]

thank you so much! this was extremely relevant, clear, and funny. Cheers

[DougHExcel]

nice, nowadays my preference is to use PQ merge instead of vlookup!

[mattmatt]

Would it be possible to create custom type of join in PQ ? Say, that you want to merge all records from one table with their overlapping records from the second table (start date/end date). Is it possible ?
Thank you
Matt