How to do a two-way lookup during import in Excel Power Query.
Sweeps URL: Microsoft Forms
Recorded at Ignite 2019 in Orlando, this video shows you how to do a two-way lookup when merging files in Power Query - even if the fields are in the "wrong" order.
Sweeps URL: Microsoft Forms
Recorded at Ignite 2019 in Orlando, this video shows you how to do a two-way lookup when merging files in Power Query - even if the fields are in the "wrong" order.
Transcript of the video:
Learn Excel from Mr Excel Podcast Episode 2296 Power Query Two-way Lookup During Import.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen. I'm excited this week I'm recording this at Ignite 2019 in Orlando.
They built these beautiful podcast studios. I'm in Podcast Studio #2. Alright, so our problem now is going to look up on the way in but this look up has to marry 2 files based on 2 fields: Account and Center. In the original Budget, it was Account Center and Budget. In Actuals it's the wrong way; Center, Account and Actual. This is a little bit more complicated than episode 2292 earlier this week. So let's come in here today. They are already both tables, and I've given them great names of budget and actual's great. So, Data, From a Table or Range.
And then the Name is Budget.
Beautiful. Close and Load to...
Only Create a Connection. Perfect.
And we have our budget connection out there. Then come over here to the second table.
Data, From Table or Range.
Yes, here we go.
This one I'm just going to leave it in the order I could rearrange it here, but I want it to be more difficult when I do the merge. This is called Actuals. Beautiful. Close and Load to...
Only create a connection, click OK.
Beautiful, now I have my two connection-only queries.
Data, Get data, Combine queries, Merge.
All right now.
If joining two things was subtle, Joining two things by two fields is even more subtle, so we choose Budget and Actual.
In the first one it's Account and then control click on Center and see how the number one and two? Down here I have to click on account 1st and then control click on center like that. That's how we set up a two-way look up. So much easier than VLOOKUP or XLOOKUP or anything like that. I want all from the first matching from the second. 42 of 42 rows is going to be perfect. Click OK.
And you think we're done? But we're not we're not done because that second table shows up just as the word Table. The column says Table, table, table, table. Choose expand icon here. I don't need the original column names as a prefix. Boy, if I was an auditor, I'd put Center and Account in. But I'm trusting that this is going to work. The first time you would do it, of course, you're going to put in account and center, but I know this works, so I choose OK, alright, so there's my Account. There's my Center. There's my Budget and Actual. Score! It works.
Alright, Close and Load and will have a brand new worksheet that combines account center, budget and actual. A two way look up. I love power query. Power query is absolutely awesome. Hey check out my book Mr Excel, LX the Holy Grail of Excel Tips has lots of stuff on power query plus lots of Excel things like that. I want to thank Microsoft for hosting me today. Thanks for listening to our podcast. We recorded at Microsoft Ignite and Microsoft is giving away Microsoft Surface air buds. Visit AKA Dot Ms Slash PodcastSweepstakes before December 15th, 2019. If you like what you see here, please down below SUBSCRIBE and ring that Bell. Ringing the Bell is the important part. It let's you get notifications in YouTube when we publish new stuff. I want to thank you for stopping by today.
I want to thank Microsoft for hosting the here in their Podcast Center at MS Ignite. We will see you next time for another netcast from MrExcel. [ Begin Outtake ] Right here we are now recording episode #5 here in our podcast booth. The show is going on behind me. This is really cool. To have all this equipment. Checkout that nice podcast center MS Ignite mug.
Alright, so let's let's go here. All right, so here we go.
We're going to click record beautiful here we go 3, 2, 1.
Learn Excel from Mr Excel Podcast Episode 2296 power query two way look up during import.
[ End Outtake ]
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen. I'm excited this week I'm recording this at Ignite 2019 in Orlando.
They built these beautiful podcast studios. I'm in Podcast Studio #2. Alright, so our problem now is going to look up on the way in but this look up has to marry 2 files based on 2 fields: Account and Center. In the original Budget, it was Account Center and Budget. In Actuals it's the wrong way; Center, Account and Actual. This is a little bit more complicated than episode 2292 earlier this week. So let's come in here today. They are already both tables, and I've given them great names of budget and actual's great. So, Data, From a Table or Range.
And then the Name is Budget.
Beautiful. Close and Load to...
Only Create a Connection. Perfect.
And we have our budget connection out there. Then come over here to the second table.
Data, From Table or Range.
Yes, here we go.
This one I'm just going to leave it in the order I could rearrange it here, but I want it to be more difficult when I do the merge. This is called Actuals. Beautiful. Close and Load to...
Only create a connection, click OK.
Beautiful, now I have my two connection-only queries.
Data, Get data, Combine queries, Merge.
All right now.
If joining two things was subtle, Joining two things by two fields is even more subtle, so we choose Budget and Actual.
In the first one it's Account and then control click on Center and see how the number one and two? Down here I have to click on account 1st and then control click on center like that. That's how we set up a two-way look up. So much easier than VLOOKUP or XLOOKUP or anything like that. I want all from the first matching from the second. 42 of 42 rows is going to be perfect. Click OK.
And you think we're done? But we're not we're not done because that second table shows up just as the word Table. The column says Table, table, table, table. Choose expand icon here. I don't need the original column names as a prefix. Boy, if I was an auditor, I'd put Center and Account in. But I'm trusting that this is going to work. The first time you would do it, of course, you're going to put in account and center, but I know this works, so I choose OK, alright, so there's my Account. There's my Center. There's my Budget and Actual. Score! It works.
Alright, Close and Load and will have a brand new worksheet that combines account center, budget and actual. A two way look up. I love power query. Power query is absolutely awesome. Hey check out my book Mr Excel, LX the Holy Grail of Excel Tips has lots of stuff on power query plus lots of Excel things like that. I want to thank Microsoft for hosting me today. Thanks for listening to our podcast. We recorded at Microsoft Ignite and Microsoft is giving away Microsoft Surface air buds. Visit AKA Dot Ms Slash PodcastSweepstakes before December 15th, 2019. If you like what you see here, please down below SUBSCRIBE and ring that Bell. Ringing the Bell is the important part. It let's you get notifications in YouTube when we publish new stuff. I want to thank you for stopping by today.
I want to thank Microsoft for hosting the here in their Podcast Center at MS Ignite. We will see you next time for another netcast from MrExcel. [ Begin Outtake ] Right here we are now recording episode #5 here in our podcast booth. The show is going on behind me. This is really cool. To have all this equipment. Checkout that nice podcast center MS Ignite mug.
Alright, so let's let's go here. All right, so here we go.
We're going to click record beautiful here we go 3, 2, 1.
Learn Excel from Mr Excel Podcast Episode 2296 power query two way look up during import.
[ End Outtake ]
