Power Query Web connector - Power Query (2023)

  • Article
  • 9 minutes to read


Release StateGeneral Availability
ProductsPower BI (Datasets)
Power BI (Dataflows)
Power Apps (Dataflows)
Dynamics 365 Customer Insights
Authentication Types SupportedAnonymous
Organizational Account
Function Reference DocumentationWeb.Page


Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.


  • Internet Explorer 10

Capabilities supported

  • Basic
  • Advanced
    • URL parts
    • Command timeout
    • HTTP request header parameters

Load Web data using Power Query Desktop

To load data from a web site with Power Query Desktop:

  1. Select Get Data > Web in Power BI or From Web in the Data ribbon in Excel.

  2. Choose the Basic button and enter a URL address in the text box. For example, enter https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States. Then select OK.

    Power Query Web connector - Power Query (1)

    If the URL address you enter is invalid, a Power Query Web connector - Power Query (2) warning icon will appear next to the URL textbox.

    If you need to construct a more advanced URL before you connect to the website, go to Load Web data using an advanced URL.

  3. Select the authentication method to use for this web site. In this example, select Anonymous. Then select the level to you want to apply these settings to—in this case, https://en.wikipedia.org/. Then select Connect.

    Power Query Web connector - Power Query (3)

    The available authentication methods for this connector are:

    (Video) Import from Web in Excel with Power Query

    • Anonymous: Select this authentication method if the web page doesn't require any credentials.

    • Windows: Select this authentication method if the web page requires your Windows credentials.

    • Basic: Select this authentication method if the web page requires a basic user name and password.

    • Web API: Select this method if the web resource that you’re connecting to uses an API Key for authentication purposes.

    • Organizational account: Select this authentication method if the web page requires organizational account credentials.


    When uploading the report to the Power BI service, only the anonymous, Windows and basic authentication methods are available.

    The level you select for the authentication method determines what part of a URL will have the authentication method applied to it. If you select the top-level web address, the authentication method you select here will be used for that URL address or any subaddress within that address. However, you might not want to set the top URL address to a specific authentication method because different subaddresses could require different authentication methods. For example, if you were accessing two separate folders of a single SharePoint site and wanted to use different Microsoft Accounts to access each one.

    Once you've set the authentication method for a specific web site address, you won't need to select the authentication method for that URL address or any subaddress again. For example, if you select the https://en.wikipedia.org/ address in this dialog, any web page that begins with this address won't require that you select the authentication method again.


    If you need to change the authentication method later, go to Changing the authentication method.

  4. From the Navigator dialog, you can select a table, then either transform the data in the Power Query editor by selecting Transform Data, or load the data by selecting Load.

    (Video) Getting Started with Power Query APIs - It's surprisingly easy!

    Power Query Web connector - Power Query (4)

    The right side of the Navigator dialog displays the contents of the table you select to transform or load. If you're uncertain which table contains the data you're interested in, you can select the Web View tab. The web view lets you see the entire contents of the web page, and highlights each of the tables that have been detected on that site. You can select the check box above the highlighted table to obtain the data from that table.

    On the lower left side of the Navigator dialog, you can also select the Add table using examples button. This selection presents an interactive window where you can preview the content of the web page and enter sample values of the data you want to extract. For more information on using this feature, go to Get webpage data by providing examples.

Load Web data using Power Query Online

To load data from a web site with Power Query Online:

  1. From the Get Data dialog box, select either Web page or Web API.

    Power Query Web connector - Power Query (5)

    In most cases, you'll want to select the Web page connector. For security reasons, you'll need to use an on-premises data gateway with this connector. The Web Page connector requires a gateway because HTML pages are retrieved using a browser control, which involves potential security concerns. This concern isn't an issue with Web API connector, as it doesn't use a browser control.

    In some cases, you might want to use a URL that points at either an API or a file stored on the web. In those scenarios, the Web API connector (or file-specific connectors) would allow you to move forward without using an on-premises data gateway.

    Also note that if your URL points to a file, you should use the specific file connector instead of the Web page connector.

  2. Enter a URL address in the text box. For this example, enter https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States.

    Power Query Web connector - Power Query (6)

  3. Select the name of your on-premises data gateway.

    Power Query Web connector - Power Query (7)

  4. Select the authentication method you'll use to connect to the web page.

    (Video) Excel Power Query Web Scraping, Custom Functions & Parameters - Part 3

    Power Query Web connector - Power Query (8)

    The available authentication methods for this connector are:

    • Anonymous: Select this authentication method if the web page doesn't require any credentials.

    • Windows: Select this authentication method if the web page requires your Windows credentials.

    • Basic: Select this authentication method if the web page requires a basic user name and password.

    • Organizational account: Select this authentication method if the web page requires organizational account credentials.

    Once you've chosen the authentication method, select Next.

  5. From the Navigator dialog, you can select a table, then transform the data in the Power Query Editor by selecting Transform Data.

    Power Query Web connector - Power Query (9)

Load Web data using an advanced URL

When you select Get Data > From Web in Power Query Desktop, in most instances you'll enter URLs in the Basic setting. However, in some cases you may want to assemble a URL from its separate parts, set a timeout for the connection, or provide individualized URL header data. In this case, select the Advanced option in the From Web dialog box.

Power Query Web connector - Power Query (10)

Use the URL parts section of the dialog to assemble the URL you want to use to get data. The first part of the URL in the URL parts section most likely would consist of the scheme, authority, and path of the URI (for example, http://contoso.com/products/). The second text box could include any queries or fragments that you would use to filter the information provided to the web site. If you need to add more than one part, select Add part to add another URL fragment text box. As you enter each part of the URL, the complete URL that will be used when you select OK is displayed in the URL preview box.

Depending on how long the POST request takes to process data, you may need to prolong the time the request continues to stay connected to the web site. The default timeout for both POST and GET is 100 seconds. If this timeout is too short, you can use the optional Command timeout in minutes to extend the number of minutes you stay connected.

You can also add specific request headers to the POST you send to the web site using the optional HTTP request header parameters drop-down box. The following table describes the request headers you can select.

(Video) Scrape Data from Multiple Web Pages with Power Query

Request HeaderDescription
AcceptSpecifies the response media types that are acceptable.
Accept-CharsetIndicates which character sets are acceptable in the textual response content.
Accept-EncodingIndicates what response content encodings are acceptable in the response.
Accept-LanguageIndicates the set of natural languages that are preferred in the response.
Cache-ControlIndicates the caching policies, specified by directives, in client requests and server responses.
Content-TypeIndicates the media type of the content.
If-Modified-SinceConditionally determines if the web content has been changed since the date specified in this field. If the content hasn't changed, the server responds with only the headers that have a 304 status code. If the content has changed, the server will return the requested resource along with a status code of 200.
PreferIndicates that particular server behaviors are preferred by the client, but aren't required for successful completion of the request.
RangeSpecifies one or more subranges of the selected representation data.
RefererSpecifies a URI reference for the resource from which the target URI was obtained.

Import files from the web

Normally when you import a local on-premises file in Power Query Desktop, you'll use the specific file-type connector to import that file, for example, the JSON connector to import a JSON file or the CSV connector to import a CSV file. However, if you're using Power Query Desktop and the file you want to import is located on the web, you must use the Web connector to import that file. As in the local case, you'll then be presented with the table that the connector loads by default, which you can then either Load or Transform.

The following file types are supported by the Web Connector:

  • Access database
  • CSV document
  • Excel workbook
  • JSON
  • Text file
  • HTML page
  • XML tables
  • PDF

For example, you could use the following steps to import a JSON file on the https://contoso.com/products web site:

  1. From the Get Data dialog box, select the Web connector.

  2. Choose the Basic button and enter the address in the URL box, for example:


    Power Query Web connector - Power Query (11)

  3. Select OK.

  4. If this is the first time you're visiting this URL, select Anonymous as the authentication type, and then select Connect.

  5. Power Query Editor will now open with the data imported from the JSON file. Select the View tab in the Power Query Editor, then select Formula Bar to turn on the formula bar in the editor.

    Power Query Web connector - Power Query (12)

    As you can see, the Web connector returns the web contents from the URL you supplied, and then automatically wraps the web contents in the appropriate document type specified by the URL (Json.Document in this example).

Handling dynamic web pages

Web pages that load their content dynamically might require special handling. If you notice sporadic errors in your web queries, it's possible that you're trying to access a dynamic web page. One common example of this type of error is:

  1. You refresh the site.
  2. You see an error (for example, "the column 'Foo' of the table wasn't found").
  3. You refresh the site again.
  4. No error occurs.

These kinds of issues are usually due to timing. Pages that load their content dynamically can sometimes be inconsistent since the content can change after the browser considers loading complete. Sometimes Web.BrowserContents downloads the HTML after all the dynamic content has loaded. Other times the changes are still in progress when it downloads the HTML, leading to sporadic errors.

(Video) Trick to connect to .asp website using Web in Power Query

The solution is to pass the WaitFor option to Web.BrowserContents, which indicates either a selector or a length of time that should be waited for before downloading the HTML.

How can you tell if a page is dynamic? Usually it's pretty simple. Open the page in a browser and watch it load. If the content shows up right away, it's a regular HTML page. If it appears dynamically or changes over time, it's a dynamic page.

See also

  • Extract data from a Web page by example
  • Troubleshooting the Power Query Web connector


How do I use Power Query in Excel Web? ›

New in Excel for the web: Power Query Refresh is now generally available for selected data sources
  1. Select the Data tab > then choose Refresh All.
  2. Open the Queries Pane > then select Refresh.
May 10, 2022

How to extract data from multiple Web pages with Power Query? ›

There are 4 steps required to scrape data from multiple web pages with Power Query.
  1. Step 1: Convert Query to a Function. Starting off where we left the last example, we need to go to the Query Editor Home tab and open the Advanced Editor. ...
  2. Step 2: Generate Page Start Numbers. ...
  3. Step 3: Invoke Custom Function.
May 21, 2019

How do I extract specific data from a website? ›

Web scraping is an automated method of collecting data from web pages. Data is extracted from web pages using software called web scrapers, which are basically web bots.
How to extract data from a website
  1. Code a web scraper with Python. ...
  2. Use a data service. ...
  3. Use Excel for data extraction. ...
  4. Web scraping tools.
Mar 3, 2022

How do I extract dynamic data from a website? ›

There are two approaches to scraping a dynamic webpage:
  1. Scrape the content directly from the JavaScript.
  2. Scrape the website as we view it in our browser — using Python packages capable of executing the JavaScript.

Can you use Power Query online? ›

Connect to an Excel workbook from Power Query Online

To make the connection from Power Query Online: Select the Excel option in the connector selection. In the Excel dialog box that appears, provide the path to the Excel workbook. If necessary, select an on-premises data gateway to access the Excel workbook.

What is an Excel Web query file? ›

A web query is a text file having a file extension of .

A web query file contains the URL of the web page that holds the data. The query becomes part of the Excel worksheet. The result set of a query is called a QueryTable.

How do I connect Excel Power Query to SharePoint? ›

The first thing you'll need to do is to get the URL of your SharePoint Library. Note this will be something like https://yourdomain.sharepoint.com/sites/yourlibrary. Now, from Power BI (or Power Query in Excel) select Get data and choose SharePoint folder, and then click Connect.

How do I extract data from a Web page automatically in Excel? ›

Select Data > Get & Transform > From Web. Press CTRL+V to paste the URL into the text box, and then select OK. In the Navigator pane, under Display Options, select the Results table. Power Query will preview it for you in the Table View pane on the right.

How do I extract all pages from a website? ›

How do we do web scraping?
  1. Inspect the website HTML that you want to crawl.
  2. Access URL of the website using code and download all the HTML contents on the page.
  3. Format the downloaded content into a readable format.
  4. Extract out useful information and save it into a structured format.
Jul 15, 2020

How do I scrape data from a website to excel? ›

Get Web Data Using Excel Web Queries
  1. Go to Data > Get External Data > From Web.
  2. A browser window named “New Web Query” will appear.
  3. In the address bar, write the web address. ...
  4. The page will load and will show yellow icons against data/tables.
  5. Select the appropriate one.
  6. Press the Import button.
Dec 21, 2022

Is Power Query better than VBA? ›

Power Query can easily replace VBA (Visual Basic for Applications) as it enables you to: Process your tables simply by clicking on buttons. Plus, no coding skills are needed! Visualize your operations step-by-step without running a single macro.

Can I Automate Power Query? ›

With Power Query, we can automate our report by developing a query that pulls data from all the files in a given folder to create a single data set. We can quickly create a PivotTable from this single data set to summarize the transactions by general ledger account and by month.

Is Power Query and M query same? ›

Power Query is where you pull your data into Power BI. M is the coding language used by Powery Query. You can use Power Query by pointing and clicking and the code in M will essentially be created for you. You can also write your own code in M directly.

Is it illegal to pull data from a website? ›

Web scraping is completely legal if you scrape data publicly available on the internet. But some kinds of data are protected by international regulations, so be careful scraping personal data, intellectual property, or confidential data.

Can you scrape dynamic content from a website? ›

Dynamic websites are based on code that is rendered once they are loaded on a browser. Therefore, the content to be scraped technically does not exist before the page is loaded. This requires the web scraping process to include a step for rendering the page content on a browser.

Is Power Query available for Excel 365 online? ›

The Power Query experience is available in all Excel 2016 or later Windows stand alone versions and Microsoft 365 subscription plans on the Data tab in the Get & Transform group.

How do I use Power Query in Office 365? ›

Load a query from the Queries and Connections pane

In Excel, select Data > Queries & Connections, and then select the Queries tab. In the list of queries, locate the query, right click the query, and then select Load To. The Import Data dialog box appears. Decide how you want to import the data, and then select OK.

Can Power Query run on SharePoint? ›

Connect to a SharePoint folder from Power Query Online

To connect to a SharePoint folder: From the Data sources page, select SharePoint folder. Paste the SharePoint site URL you copied in Determine the site URL to the Site URL text box in the SharePoint folder dialog box.

What are Web queries used for? ›

A web query or web search query is a query that a user enters into a web search engine to satisfy their information needs. Web search queries are distinctive in that they are often plain text and boolean search directives are rarely used.

How do I open a Microsoft web query file? ›

To open a saved query from Excel:
  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query. The Choose Data Source dialog box is displayed.
  2. In the Choose Data Source dialog box, click the Queries tab.
  3. Double-click the saved query that you want to open.

Is Excel web same as desktop? ›

Excel Online version looks just the same as the desktop version of Excel. With Excel online being cloud-based, you get an easier collaboration. Access any file from any device, at any time. You don't need to worry about not being able to use the Excel files created in Excel online in your Excel desktop app.

How do I link a SharePoint file to a Power Query? ›

To connect to a SharePoint folder in Power Query, go to Data > Get Data > From File > From SharePoint Folder. Enter the folder path from above and click OK.

How do I connect Power Query to access database in Excel? ›

Connect to an Access database from Power Query Desktop

Select the Access database option in the connector selection. Browse for and select the Access database you want to load. Then select Open. If the Access database is online, use the Web connector to connect to the database.

How do I link an Access query to SharePoint? ›

A linked SharePoint list or Access database updates both ways.
  1. On the External Data tab, select More > SharePoint List.
  2. Specify the SharePoint site.
  3. Select Link to the data source by creating a linked table, and then click Next.
  4. Select the list you want to link to, and then click OK.

Can Excel VBA pull data from a website? ›

VBA extends the capabilities of Microsoft Office tools and allows users to develop advanced functions and complex automation. VBA can also be used to write macros to pull data from websites into Excel.

How do I copy all content from a website? ›

Ask Leo says you can use the Ctrl+A keyboard command to select everything on the page, then Ctrl+C to copy everything.

Which method is used to extract a webpage? ›

Web scraping is an automated method used to extract large amounts of data from websites. The data on the websites are unstructured. Web scraping helps collect these unstructured data and store it in a structured form.

How do I retrieve data from a website in Excel? ›

Select Data > Get & Transform > From Web. Press CTRL+V to paste the URL into the text box, and then select OK. In the Navigator pane, under Display Options, select the Results table.

How can I get data from a website to inspect? ›

Inspecting the HTML of a Website

Firstly load the web page you want to scrape from. Right click on the page and select inspect. This will load the HTML of the website which shows the make-up of the website. Select the tool at the top left of the pane to highlight the code responsible for each part of the web page.

How do I extract data from a website to Excel using macros? ›

Step 1) Open an Excel-based Macro and access the developer option of excel. Step 2) Select Visual Basic option under Developer ribbon. Step 3) Insert a new module. Step 5) Access the reference option under the tool tab and reference Microsoft HTML Object Library and Microsoft internet control.

Which websites allow web scraping? ›

Top 10 Most Scraped Websites in 2023
  • Table of Contents.
  • Overview.
  • Top 10. Mercadolibre.
  • Top 09. Twitter.
  • Top 8. Indeed.
  • Top 7. Tripadvisor.
  • Top 6. Google.
  • Top 5. Yellowpages.
Dec 14, 2022


1. Get Data From SharePoint or OneDrive with Power Query - Demystified!
2. Advanced Power Query Tutorial | Complete End-To-End CASE STUDY | Practical guide with Dataset #Excel
(Analytical Guy)
3. Get Data From Web Using an Example Column in Power Query
(BI Gorilla)
4. Microsoft Power BI: Building connectors - BRK4003
(Microsoft Power BI)
5. Excel Magic: Importing Data from Websites in Seconds in excel #shorts
(Tips & Tricks)
6. How to scrape website links with Power Query
Top Articles
Latest Posts
Article information

Author: Reed Wilderman

Last Updated: 04/18/2023

Views: 5919

Rating: 4.1 / 5 (72 voted)

Reviews: 87% of readers found this page helpful

Author information

Name: Reed Wilderman

Birthday: 1992-06-14

Address: 998 Estell Village, Lake Oscarberg, SD 48713-6877

Phone: +21813267449721

Job: Technology Engineer

Hobby: Swimming, Do it yourself, Beekeeping, Lapidary, Cosplaying, Hiking, Graffiti

Introduction: My name is Reed Wilderman, I am a faithful, bright, lucky, adventurous, lively, rich, vast person who loves writing and wants to share my knowledge and understanding with you.