Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Power Query – Import Data from the Web

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 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. information

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?

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

Get data form the Web

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

From Web Window - Paste URL

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.

Navigator - Select Table

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

Preview Window first view of 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!

FX Rates in Excel

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

Data - Queries & Connections

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

Queries & Connection Pane

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(""))


= Web.Page(Web.Contents(""))

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

Error after changing URL

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:

Preview Window second view of data

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).

Parameter Error Message

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

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

Table with URLs

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


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 =>
    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}})
    #"Changed Type1"

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

The Power Query window will change to look like this:

Power Query after FX implemented

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.

Custom Function - icon changed

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:


Then click OK.

Add Custom Column Window

Click Continue on the privacy warning.

Privacy warning - click Continue

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

Ignore Privacy Levels

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.

Expand to show FX Rates

The data from has been imported into the query.

Expanded query showing the rates

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.

Table with all URLs

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 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.

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

13 thoughts on “Power Query – Import Data from the Web

  1. Martina Campoamor says:

    This was AMAZING! Thank you so much. This is exactly what I needed to do and I have now implemented.

    My only hitch is that I want to select the currency on a summary page and have the table refresh. I created a macro to do this, but then table has to stay visible (I’d like it to be hidden).

    If you have another posting that explains how to make a macro work (I simply recorded it) so that I can hide the data table, I’d appreciate being pointed in the right direction.

    Meanwhile, it’s all perfectly functional. Just this cosmetic thing. Thanks!

    • Excel Off The Grid says:

      For the techniques described in this post, the web data must be in a format that can be read by Power Query. There are other more advanced options, but I’ve not used them before.

  2. FRANK BURROWS says:

    Great summary of Power Query, thanks for that.

    I’m trying to pull “live” precious metals prices into my Excel workbook from the Monex website using this link:

    It grinds away but never gets any data. Any idea what I might be able to do to get it working? Thanks!

    • Excel Off The Grid says:

      This ease of this method is dependent on the layout of the webpage. Based on my testing, the webpage can be extracted, but it’s getting to the data which is the issue. You’ll need to keep drilling into the table and seeing if you can get anything sensible out of it.

  3. Francesca Hayns says:

    This was really helpful – I am very grateful – thank you.
    One little enhancement which would make my life even easier is if the code could somehow look up the current date and insert it in the source look-up instead of my having to edit it each day. Is this possible?
    Thank you

    • Excel Off The Grid says:

      This may be one of those cases that I referred to in the introduction:
      “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.”

      Therefore it may not be possible with Power Query. You could look at the automation options with Power Automate and Power Automate Deskeop

  4. jitin mathur says:

    I am getting this error in “Imported FXRates” Column:

    An error occurred in the ‘’ query. DataSource.Error: The remote server return an HTTP status code ‘404’ when trying to access ‘’.

    • Excel Off The Grid says:

      A HTTP 404 error is where the website page cannot be found. So it may have been that the website was down at the time.

      If it’s not that, I wonder if there should be a “/” at the end of:


Leave a Reply

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