Today, we're going to import
data from the web to Excel. We're going to be using get and transform from the data tab. This used to be the Power Query add-in, but since Excel 2016,
it's a part of Excel. It's a super useful feature that connects to different data sources,
transform and loads data for you so that you can do further analysis on it. If this sounds complicated
to you, don't worry. All of this is going to
be done with a few clicks, and the best part is that
once we have it set up, the data can be refreshed
with just a click of a button. Let's get to it. (upbeat music) Let's do two examples. In the first case, we're
going to import spot prices for petroleum from a web page into Excel. In the second case, we're going
to import the weather forecast for the next 10 days. Now, we're going to do
it in a dynamic way, so the data that we import
is going to be connected to the original web page. So whenever we want to get
the newest information, all we have to do is refresh our table. Let's start with spot prices. First step is to find the website from which you want to
import the data from. In this case, I'm going to import the data from the US Energy
Information Administration. Let's go to sources,
petroleum and other liquids. That's the information that I want. Now, obviously, one way
of getting this into Excel is to just copy this,
press Ctrl+C, go to Excel, press Ctrl+V, and if you're lucky, everything is going to be
recognized properly by Excel. The downside of this is it's
not connected to that web page. So if you need this information
on a continuous basis, you have to repeat the steps. What we're going to do instead
is to connect this information to that page so that all we need to do is open our Excel file
and refresh our data. So let's just remove
this, let's go and grab the URL of that page. Let's go back to Excel,
go to the data tab, and get the data from web. Paste the URL, click on OK. This is going to launch the query editor, and it's going to try to
find the tables on this page. Now, we have the ability to switch from table view to the web page view, just to make sure that we
are on the correct page. Now, you can switch back by
selecting table view here. This looks like the
table I want to import. If I'm happy with this
and I don't want to make any additional changes on this
or add any transformation, I can load this directly to my page. And if I want to load
it to my existing page, I'm going to go with load to
and select existing worksheet, click on OK. This is going to create
a query to that page. We can see the query right here. When I hover over it, we can
see when it was last refreshed and the data source
that it's connected to. This all looks good,
but let's say the fact that these are empty is
going to give me problems in my further analysis, so
let's assume I have formulas that are referencing this and
I need these to be filled. I can go and add a
transformation step to this. So let's launch Power Query again, I'm going to double
click on the query here and add a step to fill these down. So let's go to transform. Up here, we can see fill and fill down. Nothing happened when I clicked on this, although I can see the step here, but I don't see these
being filled down here. So it looks like these are not recognized as empty by Power Query. So I'm going to add a step
before I fill this down, and I'm going to make sure
that these are really empty. So with this highlighted,
I'm going to go back to the home tab, click on replace values. It asked me if I'm sure I want
to insert a step in between. Yes, I do, so go with insert. The value to find is just an empty string, and the value I want to
replace it with is null. Now, this is seen by Power
Query as really empty. So when I go to the fill down step, I can see everything being
filled down correctly. So I'm going to update this name, call it SpotPrices, press
Enter, close and load. Now my query is updated. Every time I want to
grab the new information, all I have to do is go up here and refresh or right mouse click
and refresh the query. But you also have some query options. If you click on the drop down arrow here and go to connection properties, you can refresh this every 60 minutes or adjust the minutes from here. You can also refresh data
when you open the file. So I'm just going to remove these
in this case and go with OK. That was really easy, right? So now let's take a look at importing the weather forecast for the next 10 days. Imagine you get a new job at a hotel and your boss asks you to print out the weather forecast for the next 10 days. And this is something you
need updated every day so that the tourists that are there can always take a look
at the weather forecast for the next 10 days. So this is what you do. You come and you just
Google weather forecast for New York City, so that's
where you're working, for, let's do 10 days. We get weather.com. This looks good, that's the
information I want to get in Excel and have it
connected to this page. So now that I have the URL, I'm
going to copy it, go to data, from web, and paste our
URL in here, click on OK. It's creating a connection to weather.com, it's recognized the different
tables we have here. That's the one we want. Now, there is some information in here that I don't need, like these ones. And you can see that the column titles, the headers, have shifted,
because description should belong here and high
and low should be for this one. So I'm just going to go
and transform the data before I load it to my Excel sheet. Let's first off delete what we don't need and remove the last three columns here. The only columns that I'm
interested in is the day, which is the first one, then
the description, and high/low. Now, these are not seen as
numbers, but in this case, that's fine, because
this is just going to be a report that I'm going
to show the tourists. I'm not going to perform any
mathematical operations on this. But if I wanted to do that, so let's say if I wanted to calculate
the Celsius values, I have to make sure these are recognized as numbers before I do
that transformation. So in this case, I'm fine with Fahrenheit and I'll just go with this
dataset, close and load to. Click on existing worksheet, click on OK. So that's the weather
forecast for the next 10 days. Now, all I have to do every day is just to refresh this to
get the latest information. But since I really want
to impress my boss, I'm going to do one additional step. I'm going to add an emoji to this so that, whenever we see rain or shower,
we see an umbrella emoji so the tourists know
they got to be equipped. Now, this is something I learned
from Frederic and from Oz. So there is a blog post
and a video on this. I'm going to add the link to these in the description of this video, so check it out, they're really fun. First step is to get
the emoji that I want. I'm going to use the Windows emojis by clicking on the Windows
button and the period. Then let's just type in
rain, that's the one I want. So let's just click away and copy this. Okay, so don't forget to copy this before you go to Power Query,
so Ctrl+C and press Enter. Let's go back to our query, so I'm just going to double click this. Now, I just realized I forgot
to name this table, NYWeather, and press Enter. Now, the next step is
to add that rain emoji. Wherever we have the word rain or shower, it should show the umbrella here. So let's add a column. I'm going to go with
conditional column in this case. Let's call this be equipped and just go through the steps here. If column name is description,
if this contains the word, I have rain here, then I
want my umbrella emoji, so I'm going to press
Ctrl+V, because remember, I copied this before I came here. Next one is else if description
contains the word shower, I want to have my umbrella emoji as well. Otherwise, I'm just going to go
with nothing and click on OK. That adds the umbrellas here. Drag it and bring it beside description. These are my steps that are going to be applied every single
time I refresh the data. We're done, let's go
back, close and load this. Okay, so that's my updated report. Now, tomorrow, when I get to work. (ticking) All I have to do is open my Excel file and refresh this sheet, and I have the updated information in here. I hope you found something new here. If you like this video,
give it a thumbs up. And don't forget to subscribe
if you haven't done so already so that you can get updates
when I put out new videos here. (rhythmic music)
