Excel Web Scraping 2026: Power Query and APIs Guide

For decades, the “From Web” button in Excel was a simple tool that tried to guess where tables were on an HTML page. It was clunky, often broke, and couldn’t handle anything complex.

In 2026, data analysis has changed. We aren’t just scraping static HTML tables anymore; we are connecting to REST APIs, handling JSON streams, and dealing with authentication. While many tutorials still teach the old “Legacy Web Query” method, it is time to move on.

In this guide, I will show you how to use Power Query to act as a legitimate web scraper, capable of handling headers and API keys—things the legacy tool could never dream of.

1. The “Old Way” vs. The “New Way”

If you are looking for the old interface where you clicked little yellow arrows next to HTML tables, you might notice it’s gone. Microsoft has hidden it deep in the settings (File > Options > Data > Show legacy data import wizards).

Why you should stop using Legacy Web Queries:

• It relies on Internet Explorer (IE) rendering, which is insecure and slow.
• It cannot parse JSON (the language of the modern web).
• It fails immediately on pages requiring cookies or custom headers.

The Solution: Power Query. It is built into Excel (Data > Get Data > From Web). It uses a powerful functional language called “M” to transform data.

2. Basic Scraping: Importing a Crypto Table

Let’s start with a simple example: pulling a static table from a website.

1. Go to the Data tab on the Ribbon.
2. Click From Web.
3. Enter the URL (e.g., a Wikipedia list of currencies or a stock ticker page).
4. Excel will analyze the page and present a “Navigator” window showing detected tables.

Figure 1: Power Query automatically detects HTML <table> tags.

3. Advanced: Injecting Headers & API Keys

This is where most analysts get stuck. If you try to connect to an API that requires an Authorization header or a specific User-Agent, the standard “From Web” dialog box doesn’t give you an option to add them.

You must use the Advanced Editor.

The Scenario

Imagine you want to pull data from a protected API endpoint. You need to send an API Key.

1. Start a “From Web” query but click Advanced instead of Basic.
2. Or, create a blank query and open the Advanced Editor.

Here is the M Code to make an authenticated request:

let
    // Define the URL
    url = "https://api.example.com/v1/data",
    
    // The Secret Sauce: Adding Headers using Web.Contents
    source = Web.Contents(url, [
        Headers = [
            "Authorization" = "Bearer YOUR_API_KEY",
            "User-Agent" = "Excel-PowerQuery/2026",
            "Accept" = "application/json"
        ]
    ]),
    
    // Parse JSON Response
    json = Json.Document(source),
    
    // Convert List to Table
    table = Table.FromList(json, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    table

4. Solving the “Dynamic Content” Problem

Excel’s Power Query engine is not a web browser. It does not execute JavaScript. If you try to scrape a site built with React, Vue, or Angular, Excel will just see the empty <div id="root"></div> shell.

To solve this, you need a Middleware—a service that renders the JavaScript for you and passes the clean HTML back to Excel. This is where Thordata SERP API becomes essential.

How to Integrate Thordata in Excel

Instead of connecting Excel directly to the target website, you connect it to Thordata’s API endpoint. We do the heavy lifting (rendering JS, rotating proxies) and return clean JSON that Excel loves.

let
    // 1. Target URL (The site you want to scrape)
    TargetURL = "https://www.amazon.com/dp/B08N5KWB9H",
    
    // 2. Thordata API Endpoint with your Token
    // Note: We use Query parameters to pass the target URL safely
    Source = Json.Document(Web.Contents("https://scraperapi.thordata.com/request", [
        Query = [
            url = TargetURL,
            token = "YOUR_THORDATA_SCRAPER_TOKEN",
            render_js = "True" // Force JS Rendering for dynamic sites
        ]
    ])),
    
    // 3. Navigate the JSON structure (e.g., getting the 'html' field)
    Data = Source[html],
    Result = Table.FromRecords({[Content = Data]})
in
    Result

5. Scaling Up: Why You Might Need Proxies

If you are simply pulling a few stock prices for a personal dashboard, your local IP is fine. But if you are trying to refresh 5,000 rows of product data every hour, the target website will ban your office IP address.

Excel does not have native support for rotating proxies. You cannot set a proxy in the “Data Connection” settings easily.

This is why using an API solution is superior for enterprise Excel usage. By routing requests through Thordata Residential Proxies (via the API method above), every single row in your spreadsheet appears to come from a different residential user, making your scraper unblockable.

Conclusion

The era of the “Legacy Web Query” is over. Modern data analysts use Power Query combined with robust APIs. By mastering the M language for custom headers and understanding when to use an external rendering service, you can turn Excel into a legitimate data ingestion engine.

For more details on the Power Query M formula language, check the official Microsoft Documentation.