Power Query offers many ways to get data to Excel. One of them is to Web Data import feature. Let’s understand how this works by importing world stock exchange closing data from Google Finance website.
[Related: Introduction to Power Query]
Importing web data in to Excel – Step by step tutorial
Note: You need Power Query for this tutorial. Install Power Query on Excel 2013 (how to) and continue reading.
- Open a blank workbook. Go to Power Query ribbon & click on From Web button. Enter the URL of the webpage from which you want to import the data.
- Your web page will be loaded in to Navigator pane. If there are any tables or other sections of the page that can be readily embedded in Excel, they will show up in the navigation tree structure. Hover on any table to see if that is the data you want. Once you identify what you need, click on Load to get this data in to Excel. If you want to pre-process the data before loading in to Excel, click on Edit.
- Let’s say you have taken Table 0, which contains stock market closing data around the world. When this is edited in Power Query window, it looks like this.
- As you can see, there are 2 problems with this data. (1) Column headers are missing (2) Column 3 should be splitted in to 2 columns.
- Renaming columns: Simply double click on column headers and write whatever header you want.
- Splitting a column: Select column 3 and Click on home > split column button in Power Query window. Specify the delimiter (in our case space should work) and click ok.
- Once column is spitted, our new set up looks like below. Column 3.2 needs further cleaning. We need to remove the brackets ( ).
- Removing the brackets: Select column 3.2 and click on Home > Replace values button. Replace ( with nothing. Repeat the replacement, but this time replace ) with nothing.
- Almost done. Our data is clean. Just change the column titles and we get this:
- Finally load this data to Excel by clicking on “Close & Load” button. Instantly, all this web data will be loaded to Excel as a new table.
How to refresh the imported data?
Simple. Do one of the below:
- Click on “Refresh all” button in Data ribbon of Excel
- Right click on Excel table with web data and choose “Refresh”
- Activate workbook queries pane (from Power Query ribbon) and refresh the query by clicking on the refresh icon at right.
Download Power Query web data – Example workbook
Please click here to download the workbook with Power Query web data extraction example. Right click on the query in workbook queries pane and edit it to understand the pre-processing steps better.
What awesome things can you do with web data in Excel?
Integrating your own data with publicly available sources can lead to interesting analysis situations. Power Query, Power BI & Power Pivot offer several ways to connect to web data (Facebook, Azure market place, Google Analytics etc.) and analyze it in Excel.
Have you tried importing web data to Excel? What has been your experience like? Please share your tips & thoughts in the comments section.
More ways to analyze web data in Excel:
Learn more about analyzing web data in Excel
6 Responses to “How to import web data to Excel using Power Query”
How to import data from the web using Power Query.....nice.
this is nice
very very good and nice. contribute a lot to my studies.
thanks that's very useful indeed! Just one question.
When u have imported a rate (example the exchange rate for the USD to the EURO) on a date (say the 15th of April 2016) that is fine and all, come the 16th of April 2016 that rate has changed from the 15th. how do you keep both the 15th and the 16th without copy paste etc, have power query append the 16th to the 15th of April for that rate?
Hi, question. I have a spreadsheet from the web that I would like to connect PQ to. However the web page provides me with a button which is a download, not a link to the spreadsheet. So right now I have to download the sheet and then run my PQ on it. Do you know of a way to connect directly to it. I copy the web URL into PQ but the data isn't available.
When importing table data from a website, via Import from Web (ie not using Power Query), I am unable to select the table data, as I do not see any yellow squares with red arrow to select the table. Do you I have turn something on in options to see these yellow boxes ? or has that functionality been removed ?