We are about to put some stuff together!
A bunch of ingredients. And this Excel tutorial is gonna taste better than any
excel tutorial you ever tasted in your life. And if it doesn’t, you gotta run to
the doctor and say, “Doctor! My tongue has malfunctioned. I’m in trouble. Please help me.”
XLOOKUP has been revised. I’m gonna show you XLOOKUP version 3.0. I’m gonna show
you the intellisense in Power Query so that we can write a formula. We’re gonna
do one of the six joins in Power Query. You ready to get to cooking this thing?
Let’s do it! We’ve got all of these trips that were
taken. We want to get the agent, the code, the airport name, and the state for the
last arrival for each of these agents. For Bristol, what was Bristol’s last trip?
we want to grab D-A-Y and I think that’s the Dayton Ohio Airport. Let’s get the
codes. Equals XLOOKUP. Look up what? Andy. Comma. Look-up array. We’re gonna look for
Andy here in this table. Comma. Return what? You want to return the last arrival.
All right. Comma. If not found … this is what’s new
about XLOOKUP version 3.0. The “if not found” has been moved to the fourth
position and is no longer in the sixth position. All right.
If somebody is not found, let’s put “No Travel.” Double-quote. Comma. Search mode.
Exact match. Comma. Search last-to-first. Negative-1. Close parenthesis. Enter. All
right. Andy has not traveled. Let’s send this down.
Now, how do we get the airport name and the state? I’ve got a URL where I’m going
to import from a web page and then we’ve got to clean this data up. All right.
Data. Get data. From other sources. From web. Here’s the URL. Paste. Okay. What do we
want to get? Let’s see. Document? No. Table. That’s what we want. All right. So, I’m gonna transform the data. Now this is tricky. Look how this data is set up. We’ve got
Alabama and AL. And then all of the airports in Alabama. And then it skips to
Alaska. We need a column for the states. But the
states are mixed in with the airport names. Here’s how we’re gonna dig this
out. I’m gonna add a column. Custom column. Now I know that each state has a
two-letter abbreviation and every airport has a three-letter abbreviation.
Here we go. I’m gonna just put in ‘length.’ Look at that. Now intellisense is giving
me some suggestions. All right. I think text-length is what we want. Click that.
Open parenthesis. And I want to look at the text length in column 2. Close
parenthesis. Enter. Look at that. It is getting the text length. So, I’m gonna go
back and modify this formula. IF that text length
is equal to two then grab what’s in column one. ELSE null. Okay. Look at that.
Beautiful. Right-click. Fill down. Now we’ve got to get rid of the states.
Here’s how I’m gonna do that. Add a conditional column. If column one equals
that custom column then put an X. Otherwise, empty. Okay. Now I’m gonna
filter out everything that has an X and then get rid of this column. I’m gonna
name this query ‘Airports’ then close and load it as a connection only. Now we need
an agent’s query. Here we go! Data. Here we go.
Table has headers. Call this ‘Agents.’ I’m gonna close and load this as a
connection only. Okay. Now I’m ready to do my merge. Data. Get data. Combine queries.
Merge. I want my Agents query and then the Airports query. Wanna match Column2
with Code. Left outer join. Okay. Now we’re gonna expand. Don’t wanna use that. All right. Let’s
see. We don’t need this column right-click. Remove. Now the agent code. Airport name. Then state. Sort this. Then I’m gonna
close and load this … to the workbook. Okay. Get rid of this. That is what we wanted.
We put all the ingredients together. We used the intellisense, merges, XLOOKUP 3.0
We put a lot of stuff together in this dish. And you better believe: it’s
delicious. If you can’t taste all that goodness you’ve got to go look in the
mirror and see is your tongue still there? That’s the first thing. And if it
is, go see a doctor. See you in the next video.