Learn how to perform an exact match equivalent of the VLOOKUP function in Get & Transform. The equivalent would be a left outer join, and Oz shows how to make it happen.
- [Instructor] Now it's time to address everybody's favorite Excel function, the VLOOKUP. How do you do that in get and transform? We're going to find out right now, and if you want to follow along, pull up the exercise file called VLOOKUP false. Here is a typical VLOOKUP challenge. I've got the active team's data and I would like to fill it in by retrieving the team colors and the number of players from the all teams data set.
I don't care about the city and I already have the team data. I just need those two columns. How do you do that in VLOOKUP? We're going to do this with a left outer join. This is going to be beautiful, check it out. We got to create two queries. Data. From table. Okay. Let's rename this query. Active.
And then load this as a connection only. Load. Beautiful. Now we need our all teams query. Data. From table. Okay. Let's rename this all teams. Close and load this as a connection only. And we load this as a connection only because we didn't do anything with this data.
And if we did load it to the workbook, it would just be the same information. Also, this join, it joins the queries, not the data sets. Let's get on with that. Data. New query. Combine queries. Merge. Now we're going to do a left outer join. And we have to think of the top interface as our left side. And the left side where we want to retrieve the data to is the active.
And we want to retrieve it from all teams. We're going to match team and team. Defaulted to left outer join. Click okay. Now our left side data is already in view. It's expanded. Let's go ahead and expand the right side. What fields do we want? We don't want team, we do want colors, don't want city. And I don't want to use original column name as prefix. Okay.
We still did have the team name. Let's go back. Look at this. Okay. Un-select team. All right. Now we have what we want. Let's close and load that to the workbook. Now we have the data we want and we can see clearly that the team mad science, their team colors are white and orange. And that is the VLOOKUP, using a left outer join.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins