Sweeps URL: Microsoft Forms
Evan has two lists of names. One is Firstname Lastname and the other is LAST, FIRST. He would like to do a VLOOKUP between these. My solution today involves running both lists through Power Query and then doing a Full Outer Join while merging the two lists.
Evan has two lists of names. One is Firstname Lastname and the other is LAST, FIRST. He would like to do a VLOOKUP between these. My solution today involves running both lists through Power Query and then doing a Full Outer Join while merging the two lists.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2297.
Power Query to match Jane Doe to Doe Comma Jane.
Hey welcome back to the MrExcel Netcast. I'm Bill Jelen. Well, I'm pretty excited.
All this week.
I've been recording these episodes at Ignite here in Orlando, FL. Ignite 2019.
They set up 10 podcast booths and they let people apply and they give you one hour to record a podcast.
I'm already on my 6th one because my podcasts are nice and short.
Today this is another "how to do a look up on the way in to power query". What we have here is Evan.
Evan want to know how to match these two datasets.
He has Adam Weaver, Alex Waterton, Andrew Spain and the matching file is BAILEY comma GERALD.
This proper case this upper case? Can we match these two?
Yes we can using power query so we're going to make both of them in to tables. So Ctrl+T.
Click OK boy, I don't know.
I don't know what where Evan got this data from?
Who knows though, on the table design here, we're going to call it LeftTable.
And over here will make this one into a table = Ctrl+T and we'll call this one RightTable. Those are horrible names.
I know they're just as bad as table one and table two.
I guess when you get right down to it, but Oh well, that's the way it goes.
Data, From a Table or Range.
All right, So what we have here is we have Name and Answer.
I want to make those names into upper case.
Alright, so now what I need to do - this data over here in the Name column.
I want to right-click, Transform and say Uppercase to Convert it to upper case like that?
I also see it feels like there might be a space there at the beginning.
I'm not sure, so Transform and Trim just in case there was a leading space.
Nope OK so left table that's great. We will keep it that way.
Home, Close and Load To..., Only Create Connection.
Alright, so there we go.
There's our Table1 now with Table2. I need to turn this data back around.
I need first name and last names are going to split that at the comma, right? So from table to Data, From Table or Range.
I'll choose the name along the left hand side.
Split column by delimiter. I rave about this all the time.
They figured out the delimiters. A comma, of course they did.
That's the how brilliant Power Query is. Click OK.
Alright, so we have name and name here.
I can see that there is a leading space before this, so Transform and Trim. Trim like that.
And then we're going to do is take Name2 before Name1 right?
And I'm going to combine these two fields.
I select these two fields.
It's click the first one then control click on the second one, add column and then merge columns.
The separator is going to be a space. New column is going to be name.
Yeah, click OK.
All right now, at this point I don't need these temporary columns anymore, so I can right click and remove those.
You know, normally you think hey, we're losing some audit trail there, but no, we're not because we can come over here on the right hand side to Applied Steps and go back and see what anything looks like. Alright, that looks good.
So now Home, Close and load to..., Only Create A Connection. Click OK.
We are now ready to actually do the merge so.
Data, Get Data, Combine Queries, then Merge.
Where to start from the left table? And then choose the right table.
Name from the left table. Name from the right table.
Down here it says matches 104 of 109 rows from the first table.
Uh oh, this is the first time it hasn't been a perfect match, so I want a Full Outer Join - all rows from both.
There we are, click OK.
Here from the right table, this time we are going to have to ask for name an answer so I could have left that checked.
Use original column names this prefix. Actually, yeah, this is. I'll go with that click OK.
Right, and so you see here, that Ann Bowman was in the left table, missing from the right table, then down at the bottom there should be some people that were in the right table missing from the left table. How cool is that?
That would have been a VLOOKUP and then another VLOOKUP.
It would have been really ugly to do that in straight Excel and doing it in Power Query is a great great way to go.
Alright so there that trick for Evan, who I ran into one of my live seminars.
MrExcel LX, the Holy Grail of Excel tips that book right there?
Check that out. Microsoft because we're recording here at ignite.
They say Microsoft is giving way Microsoft Surface earbuds to our listeners. To enter, go to this URL AKA.MS/podcastsweepstakes.
I'll put the URL down there in the bottom before December 15th, 2019 for a chance to win.
If you like what you see here, please subscribe and ring that Bell down below. The Bell will get you notifications.
I want to thank Microsoft for hosting me here at the Podcast Center on the floor of Microsoft Ignite. I want to thank you for stopping by.
We will see you next time for another netcast. Thank you for stopping by.
We'll see next time for another netcast from MrExcel.
Alright, that was it. 6 podcast.
I still have 15 minutes ago, although I owe them a file, right?
So let's just let me take care of that here when we chat a little bit, this is pretty much how the MrExcel podcast goes.
Normally I set up the file, I record it and then edit it and post it for the next day or something like that. Today it was a little bit different.
This morning I set up six files to have those files already to go, so just be able to bam, bam, bam here.
Now later today at some point. I'm going to have to go through the process of.
editing these and cleaning them up, I don't do as much editing as Oz does with all the fire effects and things like that.
You know he is really good about that, but I will take out the mistakes and other things.
Power Query to match Jane Doe to Doe Comma Jane.
Hey welcome back to the MrExcel Netcast. I'm Bill Jelen. Well, I'm pretty excited.
All this week.
I've been recording these episodes at Ignite here in Orlando, FL. Ignite 2019.
They set up 10 podcast booths and they let people apply and they give you one hour to record a podcast.
I'm already on my 6th one because my podcasts are nice and short.
Today this is another "how to do a look up on the way in to power query". What we have here is Evan.
Evan want to know how to match these two datasets.
He has Adam Weaver, Alex Waterton, Andrew Spain and the matching file is BAILEY comma GERALD.
This proper case this upper case? Can we match these two?
Yes we can using power query so we're going to make both of them in to tables. So Ctrl+T.
Click OK boy, I don't know.
I don't know what where Evan got this data from?
Who knows though, on the table design here, we're going to call it LeftTable.
And over here will make this one into a table = Ctrl+T and we'll call this one RightTable. Those are horrible names.
I know they're just as bad as table one and table two.
I guess when you get right down to it, but Oh well, that's the way it goes.
Data, From a Table or Range.
All right, So what we have here is we have Name and Answer.
I want to make those names into upper case.
Alright, so now what I need to do - this data over here in the Name column.
I want to right-click, Transform and say Uppercase to Convert it to upper case like that?
I also see it feels like there might be a space there at the beginning.
I'm not sure, so Transform and Trim just in case there was a leading space.
Nope OK so left table that's great. We will keep it that way.
Home, Close and Load To..., Only Create Connection.
Alright, so there we go.
There's our Table1 now with Table2. I need to turn this data back around.
I need first name and last names are going to split that at the comma, right? So from table to Data, From Table or Range.
I'll choose the name along the left hand side.
Split column by delimiter. I rave about this all the time.
They figured out the delimiters. A comma, of course they did.
That's the how brilliant Power Query is. Click OK.
Alright, so we have name and name here.
I can see that there is a leading space before this, so Transform and Trim. Trim like that.
And then we're going to do is take Name2 before Name1 right?
And I'm going to combine these two fields.
I select these two fields.
It's click the first one then control click on the second one, add column and then merge columns.
The separator is going to be a space. New column is going to be name.
Yeah, click OK.
All right now, at this point I don't need these temporary columns anymore, so I can right click and remove those.
You know, normally you think hey, we're losing some audit trail there, but no, we're not because we can come over here on the right hand side to Applied Steps and go back and see what anything looks like. Alright, that looks good.
So now Home, Close and load to..., Only Create A Connection. Click OK.
We are now ready to actually do the merge so.
Data, Get Data, Combine Queries, then Merge.
Where to start from the left table? And then choose the right table.
Name from the left table. Name from the right table.
Down here it says matches 104 of 109 rows from the first table.
Uh oh, this is the first time it hasn't been a perfect match, so I want a Full Outer Join - all rows from both.
There we are, click OK.
Here from the right table, this time we are going to have to ask for name an answer so I could have left that checked.
Use original column names this prefix. Actually, yeah, this is. I'll go with that click OK.
Right, and so you see here, that Ann Bowman was in the left table, missing from the right table, then down at the bottom there should be some people that were in the right table missing from the left table. How cool is that?
That would have been a VLOOKUP and then another VLOOKUP.
It would have been really ugly to do that in straight Excel and doing it in Power Query is a great great way to go.
Alright so there that trick for Evan, who I ran into one of my live seminars.
MrExcel LX, the Holy Grail of Excel tips that book right there?
Check that out. Microsoft because we're recording here at ignite.
They say Microsoft is giving way Microsoft Surface earbuds to our listeners. To enter, go to this URL AKA.MS/podcastsweepstakes.
I'll put the URL down there in the bottom before December 15th, 2019 for a chance to win.
If you like what you see here, please subscribe and ring that Bell down below. The Bell will get you notifications.
I want to thank Microsoft for hosting me here at the Podcast Center on the floor of Microsoft Ignite. I want to thank you for stopping by.
We will see you next time for another netcast. Thank you for stopping by.
We'll see next time for another netcast from MrExcel.
Alright, that was it. 6 podcast.
I still have 15 minutes ago, although I owe them a file, right?
So let's just let me take care of that here when we chat a little bit, this is pretty much how the MrExcel podcast goes.
Normally I set up the file, I record it and then edit it and post it for the next day or something like that. Today it was a little bit different.
This morning I set up six files to have those files already to go, so just be able to bam, bam, bam here.
Now later today at some point. I'm going to have to go through the process of.
editing these and cleaning them up, I don't do as much editing as Oz does with all the fire effects and things like that.
You know he is really good about that, but I will take out the mistakes and other things.
