URL in video: Microsoft Forms
Recorded Live at Ignite 2019 in Orlando: How to import a file using Excel Power Query while doing 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.
Transcript of the video:
All right, hey everyone, this is Bill Jelen from MrExcel.
This is exciting. I am at Microsoft Ignite 2019. They give me this awesome podcast booth here. One of eight podcast booths. I get it for an hour and they said that they are expecting me to record 1 podcast. I could do more than that so let's dive in.
Learn Excel for MrExcel Podcast Episode 2292. Load to Power Query with VLOOKUP on the Way In. I am pretty excited. This is one of the podcasts on recording Ignite 2019. I'm Bill Jelen from MrExcel Publishing. I want to thank you for stopping by now.
I had to apply here, I told them I would be doing some Power Query on how to do a VLOOKUP on the way in. So my first episode is going to be that. What I have - I have a CSV file that looks like this with Date, Item, Quantity and Region. See they have item number but not Description, Manufacturer, or List Price and this little table right here. I need to make this table be part of that CSV file on the way in.
So using Power Query first thing we're going to take this table, make it into a table that's either Insert, Table or Home, Format as table or just simple enough Ctrl T like that.
Alright, it says my table has headers. Now they call this Table1 that's not a good name for it. Let's call this PartLookup.
or ItemLookup up is really what it called it.
But here we are, It's already called PartLookup. Now our first step. Little bit annoying this step. This is what you have to do. You have to go to Data, And choose From Table or Range.
This is loading power query power query. Of course, it is built into Excel 2016 and available as an add-in for 2013 and 2010. Alright and there is our file. It looks great. I don't need to do anything to it. It has a name PartLookup - that's beautiful.
But, don't click Close and Load instead Close and Load To...
This is the most important part right here. Say that we're only going to create a connection like that and then click OK so that just defines the power query that hey, this thing right here... this table is known as PartLookup. Now the next thing to do is get that CSV file so data from text or CSV. So sales data for 2292 like that.
Alright, they show me a preview of this and then down here Transform Data.
Alright, Here we go.
Alright, it looks good - it has date item quantity, region. Yes, there's nothing I have to do this so this is another one that I'm just going to load instead of calling it sales data 2292, I'm just going to call it sales CSV file like that.
And then close and load, but again not load to the spreadsheet yet. Close and load to...
Only create a connection.
Right now it seems crazy that we have to do these two extra steps every single time to create this connection. Only table one and sales CSV file.
All right, but that's what we have to do now. Finally, the last thing that we're going to do is merge those two things together. This is where the VLOOKUP actually happens. So Data, Get data, Combine Queries, and it's very subtle here.
There's Merge and Append. Append means that you have two files with the exact same columns.
Merge means that you are adding things in like a look up table so so they're kind of offset there, so we're going to Merge.
They want to know what our first table is. That's my SalesCSV file and they want to know my second table is. My second table is the PartLookup all right now. This is the part that I hate. I hate this part because it's not obvious what we're supposed to do. Like you know, they don't say what you should do, but what we have to do here is click item on the left hand side and then part number on the right hand side. We want to left outer join, which means everything from the 1st and the matching from the second. Stay away from the fuzzy merge.
Right now this match is 617 rows from the first table, which is beautiful, that's all the rows we have and then finally click OK.
Alright, I thought it was going to be done, but I'm not OK.
Date, Item Number, Quantity Region and then PartLookup.
In PartLookup, we're going to expand that and I'm going to say I don't need part number because I already have that. But I went description, manufacturer and list price and do I wanted it to say PartLookup.Description?
no. Why would I want that? So uncheck this box. Why is that always checked by default?
Choose OK. All right and there we go. We now have item description. Maybe it makes more sense to have the description next to the item. So will choose just description. Just drag it.
Look how easy that is to do quantity, region, manufacturer, list price. Maybe list price next to quantity like that looks good. This is finally now we can Close and Load and let it come back to a new spreadsheet in this workbook. Getting external data. Right data: Item, Description, Quantity, List price, Region, Manufacturer. Now the beautiful thing is when this sale CSV file changes. When I get a new one I just saved in the same folder, same location and then when I refresh I will have the new data loaded here.
That's the amazing power of Power Query. All right There you are. Oh boy we are coming down to 2 seminars left. This seminar - I actually have a seminar tomorrow talking about Power Query and then next week in Chattanooga, TN, then that'll be it. I've retired. I'm not going to be traveling anymore. I'll still do seminars in Florida. Feel free to come on down here to Florida on spring break and will do a seminar for if you're having a controllers conference down here. Maybe that'll work.
By the book MrExcel LX, the Holy Grail of Excel tips this book right here has all kinds of great tips and tricks on Microsoft Excel. If you like the tips on this channel please Subscribe and Ring that Bell that we will get notifications also. Hey, because we're here at Ignite. Thanks for listening to our podcast. We recorded at Microsoft ignite in Microsoft is giving away Microsoft Surface earbuds to our listeners. To enter visit.
aka.ms/podcastsweepstakes.
I'll put a link down below. Enter before December 15th, 2019. Well hey, I want to thank you for stopping by. We will see you next time for another netcast from MrExcel.
This is exciting. I am at Microsoft Ignite 2019. They give me this awesome podcast booth here. One of eight podcast booths. I get it for an hour and they said that they are expecting me to record 1 podcast. I could do more than that so let's dive in.
Learn Excel for MrExcel Podcast Episode 2292. Load to Power Query with VLOOKUP on the Way In. I am pretty excited. This is one of the podcasts on recording Ignite 2019. I'm Bill Jelen from MrExcel Publishing. I want to thank you for stopping by now.
I had to apply here, I told them I would be doing some Power Query on how to do a VLOOKUP on the way in. So my first episode is going to be that. What I have - I have a CSV file that looks like this with Date, Item, Quantity and Region. See they have item number but not Description, Manufacturer, or List Price and this little table right here. I need to make this table be part of that CSV file on the way in.
So using Power Query first thing we're going to take this table, make it into a table that's either Insert, Table or Home, Format as table or just simple enough Ctrl T like that.
Alright, it says my table has headers. Now they call this Table1 that's not a good name for it. Let's call this PartLookup.
or ItemLookup up is really what it called it.
But here we are, It's already called PartLookup. Now our first step. Little bit annoying this step. This is what you have to do. You have to go to Data, And choose From Table or Range.
This is loading power query power query. Of course, it is built into Excel 2016 and available as an add-in for 2013 and 2010. Alright and there is our file. It looks great. I don't need to do anything to it. It has a name PartLookup - that's beautiful.
But, don't click Close and Load instead Close and Load To...
This is the most important part right here. Say that we're only going to create a connection like that and then click OK so that just defines the power query that hey, this thing right here... this table is known as PartLookup. Now the next thing to do is get that CSV file so data from text or CSV. So sales data for 2292 like that.
Alright, they show me a preview of this and then down here Transform Data.
Alright, Here we go.
Alright, it looks good - it has date item quantity, region. Yes, there's nothing I have to do this so this is another one that I'm just going to load instead of calling it sales data 2292, I'm just going to call it sales CSV file like that.
And then close and load, but again not load to the spreadsheet yet. Close and load to...
Only create a connection.
Right now it seems crazy that we have to do these two extra steps every single time to create this connection. Only table one and sales CSV file.
All right, but that's what we have to do now. Finally, the last thing that we're going to do is merge those two things together. This is where the VLOOKUP actually happens. So Data, Get data, Combine Queries, and it's very subtle here.
There's Merge and Append. Append means that you have two files with the exact same columns.
Merge means that you are adding things in like a look up table so so they're kind of offset there, so we're going to Merge.
They want to know what our first table is. That's my SalesCSV file and they want to know my second table is. My second table is the PartLookup all right now. This is the part that I hate. I hate this part because it's not obvious what we're supposed to do. Like you know, they don't say what you should do, but what we have to do here is click item on the left hand side and then part number on the right hand side. We want to left outer join, which means everything from the 1st and the matching from the second. Stay away from the fuzzy merge.
Right now this match is 617 rows from the first table, which is beautiful, that's all the rows we have and then finally click OK.
Alright, I thought it was going to be done, but I'm not OK.
Date, Item Number, Quantity Region and then PartLookup.
In PartLookup, we're going to expand that and I'm going to say I don't need part number because I already have that. But I went description, manufacturer and list price and do I wanted it to say PartLookup.Description?
no. Why would I want that? So uncheck this box. Why is that always checked by default?
Choose OK. All right and there we go. We now have item description. Maybe it makes more sense to have the description next to the item. So will choose just description. Just drag it.
Look how easy that is to do quantity, region, manufacturer, list price. Maybe list price next to quantity like that looks good. This is finally now we can Close and Load and let it come back to a new spreadsheet in this workbook. Getting external data. Right data: Item, Description, Quantity, List price, Region, Manufacturer. Now the beautiful thing is when this sale CSV file changes. When I get a new one I just saved in the same folder, same location and then when I refresh I will have the new data loaded here.
That's the amazing power of Power Query. All right There you are. Oh boy we are coming down to 2 seminars left. This seminar - I actually have a seminar tomorrow talking about Power Query and then next week in Chattanooga, TN, then that'll be it. I've retired. I'm not going to be traveling anymore. I'll still do seminars in Florida. Feel free to come on down here to Florida on spring break and will do a seminar for if you're having a controllers conference down here. Maybe that'll work.
By the book MrExcel LX, the Holy Grail of Excel tips this book right here has all kinds of great tips and tricks on Microsoft Excel. If you like the tips on this channel please Subscribe and Ring that Bell that we will get notifications also. Hey, because we're here at Ignite. Thanks for listening to our podcast. We recorded at Microsoft ignite in Microsoft is giving away Microsoft Surface earbuds to our listeners. To enter visit.
aka.ms/podcastsweepstakes.
I'll put a link down below. Enter before December 15th, 2019. Well hey, I want to thank you for stopping by. We will see you next time for another netcast from MrExcel.
