Thursday, August 23, 2018

Connect to a web page (Power Query)

Connect to a web page (Power Query)

Use Excel's Get & Transform (Power Query) experience to connect to a web page and import information from different tables.

Click the Data tab, then New Query > From Other Sources > From Web.

Note: If you don't see the New Query button, click the Data tab, then From Web.

Click the Power Query ribbon, then From Web.

Note: If you don't see the Power Query ribbon, then you need to activate the Power Query add-in. Click on File > Options > Add-Ins. In the Manage section at the bottom, choose the COM Add-ins option from the drop-down list, then click Go. Click the Power Query check box, then OK. The Power Query ribbon should appear automatically, however if it doesn't, you can close and restart Excel.

Click the Power Query ribbon, then From Web.

Notes: 

  • Make sure you have downloaded and installed the Power Query Add-In.

  • If you can't download or install the Power Query add-in for administrative reasons, you can still import data from a web page through Data > Get External Data > From Web. For more details see: Get external data from a Web page.

Note: The Get & Transform (Power Query) experience isn't available in Excel 2007, however you can still import data from a web page through Data > Get External Data > From Web. For more details see: Get external data from a Web page.

Next

  1. In the From Web dialog box, enter a web page URL, then OK.

    Power Query > From Web > Input URL dialog

    In this case, we're using: http://en.wikipedia.org/wiki/UEFA_European_Football_Championship.

    If the web page requires user credentials:

    • In the Access Web dialog box, click a credentials option, and provide authentication values.

    • Click Save.

  2. Click OK.

  3. Power Query will analyze the web page, and load the Navigator pane in Table View.

    If you know which table you want to connect to, then click it from the list. For this example, we chose the Results table.

    Power Query > From Web > Navigator Table View

    Otherwise, you can switch to the Web View and pick the appropriate table manually. In this case, we've selected the Results table.

    Power Query > From Web > Navigator > Web View
  4. Click Load, and Power Query will load the web data you selected into Excel.

Learn More

See Power Query 101 if you'd like to learn more about connecting to a web page, then shaping and transforming data with the Query Editor so it's exactly how you need it.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Power Query is known as Get & Transform in Excel 2016

Import data from external data sources

No comments:

Post a Comment