The ability to get data from the web with Power Query is super handy and Power Query’s user-friendly GUI makes it quick and easy.
More importantly Power Query gives you an opportunity to clean the data before loading it into Excel. Then you can refresh it with one click to get updates.
Let’s use this English Premier League data from the Sky Sports website as the example:
Note: At the time of writing, the URL above provided the data used in this example, but the layout and content of this page is out of my control and may change at some point in the future.
How to Get Data from the Web with Power Query
Step 1: Copy the URL for the web page containing the table. I’m using https://www.skysports.com/premier-league-table
Step 2: Excel 2016 onward – Data tab > From Web
Excel 2013 and earlier – Power Query tab > From Web:
Note: If you don’t see the Power Query tab in Excel 2010 or 2013 you can download it here.
Step 3: Paste your URL into the ‘From Web’ dialog box then click OK:
Step 4: At the ‘Navigator’ dialog (image below), the left-hand pane provides a list of tables available in the web page.
The first item, ‘Document’, contains the page HTML code, so it’s of no use to us, but any remaining tables typically contain data you can get with Power Query. Remember, it will only show you tables built using HTML table tags.
Clicking on the table called 'Premier League 2018/19' gives you a preview of the data in the right-hand pane:
And selecting the ‘Web View’ places a green box around the table on the web page itself:
- You can view the Navigator dialog box in full screen by clicking the full screen icon in the top right of the dialog box.
- If you want to import multiple tables then check the ‘Select Multiple items’ box in the left-hand pane.
Step 5: Once you’ve selected the table you want to import, click on the ‘Transform Data’ button. This opens the Power Query editor window which gives you an opportunity to clean the data before loading it into Excel or the Power Pivot Data Model.
Once you’re done cleaning the data you’re ready to load it to Excel or the Power Pivot Data Model.
Step 6: Home tab > Close and Load To:
Which opens the Import Data dialog box:
Tip: If you load it to the Data Model, be sure to select ‘Only create connection’ so that you don’t duplicate the data in your file i.e. once in the worksheet and again in the Power Pivot data model.
Step 7: Refresh the data. To get updates from the web page simply go to the Data tab of the ribbon and click ‘Refresh All’:
Or if you have multiple queries you can open the queries and connections pane:
In Excel 2013 and earlier go to the Power Query tab > Show Pane :
Find the query in the list > right-click > Refresh:
*Power Query Web Limitation
To do this, right-click some empty space on the web page > View Page Source (or similar depending on the browser you use):
CTRL+F to open the Find dialog box. Enter ‘<table’. This will highlight any instances of a HTML table tag.
If you find HTML table tags then it confirms there is a table that Power Query can get from that page, however it doesn’t guarantee it’s the table you actually want, as there may be other tables on the page.
Power Query can get data from a myriad of places and has loads of tools for cleaning and transforming data. Here are some more examples:
And one of the most common tasks, unpivoting: Unpivot with Power Query
If you’d like to learn Power Query, and you should 😉, please consider my Power Query course.