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.

XLOOKUP v3.0; Power Query Import from Web; Intellisense, Text.Length

26 thoughts on “XLOOKUP v3.0; Power Query Import from Web; Intellisense, Text.Length

  • November 26, 2019 at 2:38 pm
    Permalink

    Great work as usual, I wish all my trainers were engaging like you!

    Reply
  • November 26, 2019 at 3:50 pm
    Permalink

    👍🙂👍

    Reply
  • November 26, 2019 at 4:03 pm
    Permalink

    Thanks for the video Oz!

    Reply
  • November 26, 2019 at 4:23 pm
    Permalink

    no doubt you have good understanding but your way of teaching iS NOT up to the mark. YOU TALK TO YOURSELF……..

    Reply
  • November 26, 2019 at 6:54 pm
    Permalink

    Fantastic as usual! I'm seriously going to lose my shit if Microsoft keeps this in Beta for the next year like they've done with Dynamic Arrays

    Reply
  • November 26, 2019 at 7:43 pm
    Permalink

    Marvelous work 👌. Sir how can I activate xlookup in Excel 2013.

    Reply
  • November 26, 2019 at 8:22 pm
    Permalink

    Thank you for the Delicious Excel Fun, Oz!!!!

    Reply
  • November 26, 2019 at 9:56 pm
    Permalink

    Waouh !!! Very usefull ! Thank you Excel On Fire

    Reply
  • November 26, 2019 at 11:15 pm
    Permalink

    Amazing …. thanks Oz

    Reply
  • November 27, 2019 at 12:26 am
    Permalink

    Really tasty! superb Oz!

    Reply
  • November 27, 2019 at 12:29 am
    Permalink

    Awesome!

    Reply
  • November 27, 2019 at 12:58 am
    Permalink

    "Excel on Fire tutorials are getting hotter than my whole filmography combined." (Jameson, Jenna. 2019).

    Reply
  • November 27, 2019 at 1:18 am
    Permalink

    Delicious Video Oz…Fantastic.

    Reply
  • November 27, 2019 at 1:46 am
    Permalink

    Great dish by our Excel Master Chef OZ as usual 🙂

    Reply
  • November 27, 2019 at 2:13 am
    Permalink

    Nicely done.

    I was thinking about the search mode (last-to-first) argument in XLOOKUP.
    =XLOOKUP(B4,Table1[Person],Table1[Arrival],"no travel",0,-1)
    It could go bad if some joker comes along and sorts that source table however they want, instead of by date.

    If you want the last arrival regardless of table sort order, you could add a pinch of SORTBY to your tasty XLOOKUP recipe:

    =XLOOKUP(B4,SORTBY(Table1[Person],Table1[Date]),SORTBY(Table1[Arrival],Table1[Date]),"no travel",0,-1)

    Reply
  • November 27, 2019 at 3:39 am
    Permalink

    That is amazing recipes… just love it 😍

    Reply
  • November 27, 2019 at 4:17 am
    Permalink

    You’re a Magician 🎩 🎩 🎉 !!! And that was so Yummy!! Thanks, Sir!!! So useful bravo 👍

    Reply
  • November 27, 2019 at 5:20 pm
    Permalink

    Kralsın adamım 🙂 (You are king my man :))

    Reply
  • November 27, 2019 at 10:10 pm
    Permalink

    'Del'intellicious' thanks Oz

    Reply
  • November 28, 2019 at 2:39 am
    Permalink

    Power Query with Intellsense….cool!

    Reply
  • November 28, 2019 at 2:42 pm
    Permalink

    Hi Oz.. cool challenge and solutions. What's for dessert.. haha!! Thanks for all the great videos.. good learning.. entertainment.. humor.. always a pleasure :-)) Thumbs up!

    Reply
  • November 28, 2019 at 7:49 pm
    Permalink

    Oooh! That was tasty 😅 XLOOKUP and Merge Queries in one video. I am full.

    Reply
  • November 28, 2019 at 8:23 pm
    Permalink

    Awesome!

    Reply
  • December 1, 2019 at 11:50 am
    Permalink

    Delicious! 🙂

    Reply
  • December 1, 2019 at 12:36 pm
    Permalink

    I have a request: My internet service provider keeps dropping out on me, but doesn't believe me. So I've got a simple script which pings google.com every 20 seconds, and records it with a timestamp. If there's no ping, there's no internet. I've filtered the list down to a list of timestamps of when the internet dropped out. What statistical analysis should I do to this list? Can you think of a good way to graph it? I was thinking a histogram, where the x axis is hour of the day and the y axis is number of dropouts in that hour. I can't figure out how to make it in Excel tough; probably a pivot table? Here is some sample data, happy to provide more if it helps!
    2019-11-28 18:22:43
    2019-11-28 18:22:53
    2019-11-28 18:23:03
    2019-11-28 18:23:13
    2019-11-28 19:09:04
    2019-11-28 22:57:29
    2019-11-28 23:04:29
    2019-11-29 08:57:43
    2019-11-29 08:57:53
    2019-11-29 09:01:43
    2019-11-29 09:01:53
    2019-11-29 09:02:23

    Reply
  • December 1, 2019 at 7:36 pm
    Permalink

    Beautiful. Thanks OZ.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *