Power Query – Import Data from the Web

The web is a vast place with so much information.  However, just because the data is there does not make it easy to get at  In this post, we’ll look at how we can load web data into Power Query.  While you might get excited at the thought of absorbing data from everywhere with the click of a button, it’s not a silver bullet.  Power Query can do a lot, but the website has to be in a reasonably usable format to begin with.

Anyway, enough talk, time to import some data from the web.

Basic web import

For the purpose of this exercise, we will be using https://www.xe.com to import exchange rates into Power Query.

The website contains pages which display the exchange rates for any currency on any date.  Here are the USD rates for 1 July 2019.

The most useful part of the screenshot above is the URL.  If you look closely, it contains the currency code and the date within the URL.  Hmmm… I wonder if we could use that in a more advanced way?

https://www.xe.com/currencytables/?from=USD&date=2019-07-01

Anyway, before getting onto the more advanced, let’s start with the basics.  Copy the URL, then in Excel click Data -> Get Data -> From Other Sources -> From Web

The From Web window will open.  Paste the URL we copied earlier into the URL box and click OK.

The next window to appear will be the Navigator window.  As web tables from websites can have poor naming conventions, it’s not always clear which table we need.  We must click through each until we find the one we want.  Once you have found the right table, click Transform Data.

When the Power Query Editor loads it contains all the currency data.

Give the query a useful name, such as FXRates

Click Home -> Close and Load to push the data into Excel.  How easy was that!

If the webpage contains live data, it will update the query with that live data each time we click refresh.  That’s pretty powerful stuff.

Editing the query

Now let’s edit the query to use it for different dates and currencies (in doing this, we will come across an error which we need to fix; we can handle it).

In Excel click Data -> Queries and Connections

Double click the query in the Queries and Connections pane to open the Power Query editor.

The Power Query editor will open again, click on the Source step within the Applied Steps. The URL is hardcoded into the query.  Edit the M code in the formula bar to change the query to include a different currency and a different date.  For our example, I’ve selected EUR and 01 January 2019.

The following M code:

= Web.Page(Web.Contents("https://www.xe.com/currencytables/?from=USD&date=2019-07-01"))

becomes

= Web.Page(Web.Contents("https://www.xe.com/currencytables/?from=EUR&date=2019-01-01"))

Click through the remaining steps of the query.  The Changed Type step will reveal the following error.

Looking at the M code in the Formula Bar reveals the issue; there are specific references to the USD currency in the header (see the red highlighted sections below).

= Table.TransformColumnTypes(Data0,{{"Currency code ▲▼", type text}, 
{"Currency name ▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}})

Our selected currency is now EUR; the column header would become Units per EUR and EUR per Unit.

The change in the column headers comes directly from the source webpage; therefore there is nothing we can do to stop it.  But, there are things we can do to work around it.

• Delete the Change Type step which is causing the error.
• Demote the header to the first row by clicking Transform -> User First Row as Headers (dropdown) -> Use Headers as First Row.
• Delete the top row by clicking Home -> Remove Rows -> Remove Top Rows. Enter 1 in the Number of rows box, click OK.
• Manually enter suitable column names (I’ve gone for Code, Currency, Rate and Inverted Rate).
• Apply suitable data types to each column (I’ve gone for Text, Text, Decimal, Decimal).

The Preview Window now looks like this:

Click Close & Load to push the query into Excel.  We can now change the currency and dates in the source URL meaning we can get the rate for any date we want.

Using parameters with web queries

Editing the M code each time we want to import different dates and rates isn’t ideal.  It’s time-consuming, plus can we really trust other users who know nothing about Power Query to update the step correctly.

If you’ve been following along with this Power Query series, you will remember that we previously created parameters to manage variables.  If we try this with web queries, we will come across a strange error (see the screenshot below).

The reasons for this are complicated and too technical for our introductory series.   If you want more details, check out this post.

But don’t worry, there is another way to use cells on a spreadsheet to retrieve the information we want.

Using custom formulas

In a previous post, we combined all files from a folder.  To achieve this, we used the Excel.Workbook() function to combine any file which happened to be in the folder.  We can create our own function, which rather than combining files, will return web queries based on the URLs we give it.

Let’s start with a new Table (I’ve called the table tblURLs).  It contains the following values:

• Code
• Date
• URL

The formula in  Cell C2 is constructed by concatenating the currency code and date into the URL structure.

="https://www.xe.com/currencytables/?from="&[@Code]&"&date="&TEXT([@Date],"yyyy-mm-dd")

Next, add this table into Power Query.  Select a cell inside the table, then Data -> From Table/Range.

We are now ready to create a custom function.

Creating the function

Let’s head back to the original query, by clicking the FXRates query from the queries list.

We are going to change this query into a custom function  Click Home -> Advanced Editor to display the M Code.

Change the M code as follows:

(1) Add the following code to the top of the query:

(URL) as table =>

(2) Change the source URL within the code to the letters URL, which is the variable we created above.

The M code will now be as follows (the highlighted sections are the parts we have changed).

(URL) as table =>
let
    Source = Web.Page(Web.Contents(URL)),
    Data0 = Source{0}[Data],
    #"Demoted Headers" = Table.DemoteHeaders(Data0),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "Code"}, {"Column2", "Currency"}, {"Column3", "Rate"}, {"Column4", "Inverted Rate"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Rate", type number}, {"Inverted Rate", type number}, {"Code", type text}, {"Currency", type text}})
in
    #"Changed Type1"

Click Done in the Advanced Editor window to accept the changes.

The Power Query window will change to look like this:

In the query list it shows the FX Rate query we have just changed with an fx next to it, this means it is a function.

Using the function

Open up the tblURLs query.

Click Add Column -> Custom Column

In the Custom Column window, enter the following:

New column name:

Imported FX Rates

Custom column formula:

=FXRate([URL])

Then click OK.

Click Continue on the privacy warning.

On the Privacy Levels window, check the box to Ignore Privacy Levels, then click Save.

Click the expand icon at the top of the Imported FX Rates column (the new column we just added).  Uncheck the Use original column name as prefix box, then click OK.

The data from XE.com has been imported into the query.

Tidy up the query by:

• Change the data type of each column
• Remove the columns we don’t need

Click Close & Load to push the information into Excel.

It’s time to test this out to prove that we can get exchange rates for any currency and any date.  In Excel, change the date or currency code in the tblURL Table.  Click Data -> Refresh All, the exchange rates from the query will now refresh.  How cool is that, eh?

Importing multiple web pages

So far we have imported a single web page.  But what if we wanted to import multiple web pages?  If the Custom Function is working, then this is going to be easy.

Add some more rows of data onto the tblURL table.

Click Data -> Refresh All

Check out the table of FX Rates… BOOM! (that was the sound of your mind being blown).  Power Query has imported all the dates and rates included in the tblURL’s Table.  We can get all the FX Rates for any date for any base currency.

A few warnings

This is powerful stuff. But there are a couple of things to make you aware of:

• If you call too many URL’s, the query might become slow to refresh.  Power.
• If xe.com changes their website, the query may cease to work correctly.
• We can’t get data from every website as some are not structured in the right way.

---

What next?

Discover how you can automate your work with our Excel courses and tools.