• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Get Data from the Web with Power Query

You are here: Home / Power Query / Get Data from the Web with Power Query
Get Data from the Web with Power Query
March 28, 2019 by Mynda Treacy

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.

The current Power Query limitation* is that only tables formatted in HTML <Table> tags can be easily imported by Power Query. Some web pages build tables using JavaScript, which is not covered in this tutorial.

Let’s use this English Premier League data from the Sky Sports website as the example:

English Premier League data

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

data from the web for Excel 2016 onwards

Excel 2013 and earlier – Power Query tab > From Web:

data from the web for Excel 2013 and earlier versions

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:

from web dialog box

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:

data preview on right hand pane

And selecting the ‘Web View’ places a green box around the table on the web page itself:

select web view

Tips:

  1. You can view the Navigator dialog box in full screen by clicking the full screen icon in the top right of the dialog box.
  2. 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.

click on transform data

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:

close & load to button

Which opens the Import Data dialog box:

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’:

refresh all

Or if you have multiple queries you can open the queries and connections pane:

queries and connections

In Excel 2013 and earlier go to the Power Query tab > Show Pane :

show pane

Find the query in the list > right-click > Refresh:

find the query in the list

*Power Query Web Limitation

As I mentioned earlier, Power Query is great at getting data from the web where it’s formatted as an HTML table, but not tables generated using JavaScript. You can easily tell if the table is HTML or JavaScript by inspecting the web page source code and looking for the HTML table tags.

To do this, right-click some empty space on the web page > View Page Source (or similar depending on the browser you use):

view page source

CTRL+F to open the Find dialog box. Enter ‘<table’. This will highlight any instances of a HTML table tag.

open the find dialog box

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.

More Power...Query

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:

Consolidate Excel Sheets

Get Files from a Folder

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.

Get Data from the Web with Power Query

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power Query
Previous Post:GETPIVOTDATA Function for Power PivotGETPIVOTDATA Function for Power Pivot
Next Post:Static Variables in VBAStatic variables in VBA

Reader Interactions

Comments

  1. Jayasai

    October 29, 2020 at 2:06 pm

    Dear sir,
    I am unable to find advanced option in from web popup, please help me in enabling it.

    I am using ms office 2016

    Thank you in advance waiting for your reply.

    Reply
    • Mynda Treacy

      October 29, 2020 at 2:08 pm

      Hi Jayasai, You can try updating your installation of Excel to see if you get this feature, but if it’s not there after updating, then it’s not available in your version of Excel, sorry Mynda

      Reply
  2. Peter Dube

    October 14, 2020 at 7:21 am

    I have recently upgrades to Office 365 Pro Plus. I have a practice from the past to import a HTML table from a web site into Excel that includes HTML hyperlinks. In the previous version of excel, there was an ‘options’ popup in the process and I could choose ‘full html format’ and the links were retained in excel. Now that option is gone. Are you aware of a way to load from a web data source an retain the html hyperlinks?

    Thank you

    Reply
    • Mynda Treacy

      October 14, 2020 at 11:06 am

      Hi Peter, I don’t remember being able to retain the link status when importing data from the web with Power Query, and I’m not aware of any way to do it now, sorry. Have you tried adding a column to the Excel Table that Power Query outputs with the HYPERLINK function that references the text URL and converts it to a clickable link? Mynda

      Reply
  3. Terry davies

    May 17, 2020 at 10:29 pm

    I have a power query that gets a web-based database and loads it to Excel.
    The web page consists of the last 10,000 rows of the database, with the latest row on top.
    If, when I refresh the query, 50 rows have been added, then the oldest 50 rows are no longer on the database. Therefore, when my data is refreshed I am left with the latest 10,000 rows but I have lost the oldest 50 rows.
    Is there any way I can refresh the query but not delete the older rows in my original table, so my table grows with each refresh?

    Reply
    • Mynda Treacy

      May 18, 2020 at 12:07 pm

      Hi Terry,

      Power Query is designed to get the data from the source, including any changes. If your source changes then you’d need to intervene with VBA to take a copy of the data before the refresh so you can retain those old rows. There’s no setting in Power Query to do this, sorry.

      Mynda

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.