Implementing Web Scraping and Dynamic HTML Table Extraction Pipelines in Power Query M: Parsing Nested Structures, Handling Anti-Patterns, and Building Resilient Selectors

Introduction

Picture this: your finance team needs weekly data from a government statistics portal that publishes HTML tables — no API, no download button, just a webpage with numbers embedded in markup. Or maybe your operations team tracks competitor pricing from a supplier catalog that renders its tables differently depending on the browser session. In both cases, Power Query's built-in web connector gets you through the door, but the moment the HTML structure gets even slightly irregular — nested tables, merged cells, dynamically injected class names, header rows that shift position — the default Web.Page approach collapses into an unnavigable mess of nested lists and null values.

This lesson is about going deeper. We'll move past the point-and-click "Connect to Web" experience and build M-language pipelines that can extract, navigate, validate, and normalize data from real HTML sources with real-world messiness. We're going to tear apart what Web.Page actually returns, write custom navigation logic for nested structures, and build selectors that survive the most common page evolution patterns — things like tables that gain or lose columns, headers that migrate, and class names that change between deployments.

By the end of this lesson, you'll have a production-grade HTML scraping pipeline you can actually trust in a scheduled refresh environment.

What you'll learn:

• What Web.Page and Web.BrowserContents return and how to navigate their output structures precisely
• How to extract data from complex nested and merged-cell HTML tables
• How to write resilient column selectors that survive structural drift
• How to handle common anti-patterns: header rows embedded in <tbody>, multi-level headers, tables-within-tables
• How to compose this into a reusable, parameterized scraping function

---

Prerequisites

You should be comfortable with the M language beyond the basics — specifically, working with lists, records, and nested tables. If M Language Fundamentals: Syntax, Types, and Expressions for Power Query is still fresh territory for you, review that first. You should also understand try...otherwise error handling — we'll use it heavily here. And since the structures we're parsing closely resemble what you'd encounter in XML, familiarity with Working with JSON and XML Data Sources in M: Complete Foundation Guide will help you reason about the traversal patterns.

---

What Web.Page Actually Returns

Most tutorials tell you to use Web.Page(Web.Contents("https://...")) and then navigate the Data column. What they rarely explain is why the structure looks the way it does, which means you're always one step away from being completely lost when the default navigation breaks.

Web.Page returns a table with four columns: Caption, Source, ClassName, and Data. Each row represents a distinct "thing" that Power Query detected on the page — usually an HTML table. The Data column contains the parsed table content as a nested table value.

Here's what the top-level output looks like conceptually:

Caption | Source | ClassName | Data
--------|--------|-----------|-----
"Table" | "<table>...</table>" | "wikitable" | [Table value]
"Table" | "<table>...</table>" | "" | [Table value]

The catch is that Power Query's HTML parser doesn't give you a DOM. It gives you a flattened interpretation of what it thinks are tables. Tables that contain other tables (a layout anti-pattern that's still shockingly common on government and financial data sites) get parsed in unpredictable ways — sometimes the outer table, sometimes the inner table, sometimes both, sometimes neither cleanly.

Let's start with a concrete baseline. Here's the minimal viable extraction for a well-structured page:

let
    PageUrl = "https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal)",
    
    RawPage = Web.Page(Web.Contents(PageUrl)),
    
    // Web.Page returns a table — filter to rows where Data is not null
    TablesOnly = Table.SelectRows(RawPage, each [Data] <> null),
    
    // Navigate to the first table (index 0)
    FirstTable = TablesOnly{0}[Data],
    
    // Promote the first row as headers if Web.Page didn't detect them
    Promoted = Table.PromoteHeaders(FirstTable, [PromoteAllScalars = true])
in
    Promoted

This works on clean, well-formed HTML. But "clean" and "well-formed" describe maybe 30% of the pages you'll actually encounter in the wild.

Note: Web.Page requires an internet connection at query evaluation time. In Power BI Service, this means your gateway must have outbound internet access, and the source URL must not require browser-side JavaScript to render the table. If the table is rendered by JavaScript after page load, you'll need Web.BrowserContents instead — more on that shortly.

---

Navigating the Nested Structure Precisely

The most common mistake practitioners make is using positional indexing like TablesOnly{0}[Data] and calling it done. This breaks the moment the page adds a cookie banner (which often renders as a table), an advertisement, or a navigation menu before your target table.

The right approach is to identify your target table by a stable attribute, not its position. The best candidates are:

1. CSS class name — ClassName column in Web.Page output
2. Table caption or nearby heading text — Caption column
3. Column header content — inspect the first row of Data

Here's a more resilient navigation pattern using class name:

let
    PageUrl = "https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal)",
    
    RawPage = Web.Page(Web.Contents(PageUrl)),
    
    // Filter by CSS class — Wikipedia uses "wikitable sortable"
    TargetTable = Table.SelectRows(
        RawPage,
        each Text.Contains([ClassName], "wikitable")
    ),
    
    // Validate we found exactly one match before proceeding
    TableCount = Table.RowCount(TargetTable),
    
    ValidatedTable = if TableCount = 0 then
        error Error.Record("TableNotFound", "No table with class 'wikitable' found on page", PageUrl)
    else if TableCount > 1 then
        // Multiple matches — take the largest by row count as a heuristic
        Table.First(
            Table.Sort(
                Table.AddColumn(TargetTable, "RowCount", each Table.RowCount([Data])),
                {"RowCount", Order.Descending}
            )
        )[Data]
    else
        TargetTable{0}[Data]
in
    ValidatedTable

Notice the validation logic. We're not assuming one table will be found. We handle zero matches (error with context) and multiple matches (heuristic selection by row count). This is the difference between a query that breaks silently and one that fails loudly with useful diagnostics.

Tip: Store your target CSS class name or caption pattern in a parameter table rather than hardcoding it in each query. The Cross-Query State Management and Shared Parameter Tables in Power Query M pattern is ideal here — when the class name changes on a site redesign, you update one value and all downstream queries adapt.

---

Handling the Source Column: Parsing Raw HTML with Text Functions

Sometimes Web.Page fails to correctly parse a table's structure — merged cells cause columns to collapse, header detection misfires, or nested elements get stripped. In these cases, you need to go one level lower and work directly with the Source column, which contains the raw HTML string for each detected table.

This opens up a powerful technique: using M's text manipulation functions as a lightweight HTML parser. It's not a full DOM parser — don't try to handle arbitrary HTML with it — but for well-structured, predictable table markup, it gets the job done.

Here's a function that extracts text content from the rows of a simple HTML table by parsing <tr> and <td>/<th> tags:

let
    ParseHtmlTable = (rawHtml as text) as table =>
    let
        // Normalize line breaks and strip carriage returns
        CleanHtml = Text.Replace(Text.Replace(rawHtml, "#(lf)", ""), "#(cr)", ""),
        
        // Split on closing row tag to get individual rows
        RowSplits = Text.Split(CleanHtml, "</tr>"),
        
        // Remove the last empty element from the split
        RowsOnly = List.RemoveLastN(RowSplits, 1),
        
        // For each row, extract cell content between <td> or <th> tags
        ParseRow = (rowHtml as text) as list =>
        let
            // Find all cell tags — both td and th
            CellPattern = Text.Split(rowHtml, "<td"),
            ThPattern = Text.Split(rowHtml, "<th"),
            
            // Use th if td produces only one element (header row)
            RawCells = if List.Count(CellPattern) > 1 then CellPattern else ThPattern,
            
            // Skip the first element (before the first cell tag)
            CellsWithContent = List.Skip(RawCells, 1),
            
            // Extract text between > and </td> or </th>
            ExtractText = List.Transform(
                CellsWithContent,
                (cell) =>
                let
                    AfterTag = Text.AfterDelimiter(cell, ">"),
                    BeforeClose = Text.BeforeDelimiter(AfterTag, "</t"),
                    // Strip any remaining nested tags
                    StripTags = Text.Replace(
                        Text.Replace(BeforeClose, "<span>", ""),
                        "</span>", ""
                    )
                in
                    Text.Trim(StripTags)
            )
        in
            ExtractText,
        
        // Parse all rows
        ParsedRows = List.Transform(RowsOnly, ParseRow),
        
        // First row becomes headers
        Headers = ParsedRows{0},
        DataRows = List.Skip(ParsedRows, 1),
        
        // Convert to table
        AsTable = Table.FromRows(DataRows, Headers)
    in
        AsTable
in
    ParseHtmlTable

Warning: This approach is fragile against malformed HTML and will break on tables with colspan or rowspan attributes without additional handling. Use it as a targeted tool for specific known sources, not as a general-purpose HTML parser. For sources where Web.Page works, prefer it.

---

Dealing With Merged Cells: colspan and rowspan

This is where most scrapers break, and it's one of the hardest problems to solve cleanly in M. A table that uses colspan to create grouped headers looks visually sensible but produces structurally broken output in Web.Page — columns collapse together, rows have fewer cells than expected, and Table.PromoteHeaders produces meaningless column names.

Consider a financial data table with this structure:

| Country | 2022 GDP           | 2023 GDP           |
|         | USD (bn) | % Change | USD (bn) | % Change |
|---------|----------|----------|----------|----------|
| USA     | 25,000   | 2.1%     | 26,200   | 4.8%     |

Web.Page will typically collapse the first two rows in bizarre ways. Here's the strategy:

Step 1: Detect the multi-level header by inspecting row structure.

let
    RawData = /* result of Web.Page navigation */,
    
    // Convert to list of rows for inspection
    AllRows = Table.ToRows(RawData),
    
    // Count non-null values in each row to detect header rows
    // Multi-level headers often have fewer populated cells
    RowPopulation = List.Transform(
        AllRows,
        (row) => List.Count(List.Select(row, each _ <> null and _ <> ""))
    ),
    
    // Find the row with the most populated cells — that's your data start
    MaxPopulation = List.Max(RowPopulation),
    
    // Identify header rows (rows with significantly fewer cells)
    HeaderRowCount = List.Count(
        List.Select(RowPopulation, each _ < MaxPopulation * 0.75)
    )
in
    HeaderRowCount

Step 2: Construct composite column names from multiple header rows.

let
    RawData = /* Web.Page result */,
    AllRows = Table.ToRows(RawData),
    
    // Assume first two rows are headers based on detection above
    HeaderRow1 = AllRows{0},
    HeaderRow2 = AllRows{1},
    DataRows = List.Skip(AllRows, 2),
    
    // Forward-fill the first header row to cover colspan spans
    FilledHeader1 = List.Accumulate(
        List.Positions(HeaderRow1),
        {},
        (state, i) =>
            let
                CurrentValue = HeaderRow1{i},
                PreviousValue = if List.Count(state) = 0 then "" else List.Last(state),
                FillValue = if CurrentValue = null or CurrentValue = "" 
                            then PreviousValue 
                            else CurrentValue
            in
                state & {FillValue}
    ),
    
    // Combine headers: "2022 GDP | USD (bn)", "2022 GDP | % Change", etc.
    CompositeHeaders = List.Transform(
        List.Positions(FilledHeader1),
        (i) =>
        let
            H1 = FilledHeader1{i},
            H2 = HeaderRow2{i},
            Combined = if H1 = "" or H1 = null then H2
                       else if H2 = "" or H2 = null then H1
                       else H1 & " | " & H2
        in
            Combined
    ),
    
    // Build the final table with composite headers
    FinalTable = Table.FromRows(DataRows, CompositeHeaders)
in
    FinalTable

The List.Accumulate forward-fill pattern here is the key insight. When a cell spans multiple columns with colspan, the HTML parser either leaves nulls or empty strings in the adjacent positions. By carrying the last non-empty value forward, we reconstruct what the header logically meant.

Key insight: The forward-fill accumulator is a broadly useful M pattern — it appears in Advanced M: Iterators, Accumulators, and Recursive Patterns with more detail. If your data has deeply nested structures or you need to recurse through multiple levels of header spanning, that lesson's recursive accumulator pattern is where to go next.

---

Web.BrowserContents for JavaScript-Rendered Tables

Some pages render their tables entirely via JavaScript — the initial HTML response contains only a shell, and the table data is injected by client-side code after page load. Web.Page reads the raw HTTP response, so it sees an empty shell. For these sources, you need Web.BrowserContents.

let
    PageUrl = "https://example-stats-portal.gov/economic-indicators",
    
    // Web.BrowserContents waits for page load before capturing HTML
    // This requires Power BI Desktop with the feature enabled
    RenderedHtml = Web.BrowserContents(PageUrl),
    
    // Pass the rendered HTML through Web.Page for table parsing
    ParsedPage = Web.Page(RenderedHtml),
    
    // Continue with same navigation logic as before
    TargetTable = Table.SelectRows(
        ParsedPage,
        each Text.Contains([ClassName], "data-table")
    ){0}[Data]
in
    TargetTable

Warning: Web.BrowserContents is significantly slower than Web.Page on raw Web.Contents because it must fully render the page in a headless browser. It also has limitations in Power BI Service — as of current releases, it works in Desktop but may require special configuration on gateway. Always check whether a site has an underlying API (inspect Network traffic in browser dev tools) before resorting to browser-rendered scraping. A hidden JSON API is almost always preferable.

The other practical limitation of Web.BrowserContents is that it doesn't support parameterization through query folding. Since it fires a full browser session for every call, pagination through Web.BrowserContents in a loop can be very slow. See Streaming and Pagination Patterns in M: Handling Large APIs and Multi-Page Data Sources with Custom Iterators for strategies to manage this — particularly the lazy list pattern that avoids fetching pages you don't need.

---

Building a Resilient Column Selector

A table that has 12 columns today might have 13 next month when the source adds a new metric. If your transformation pipeline references columns by position (Table.Column(data, "Column3")), it breaks silently — you get the wrong data with no error. If it references by name, it breaks loudly when the name changes — which is still a problem but at least an honest one.

The resilient approach is to select columns by semantic matching — a pattern that maps logical column names in your model to candidate names on the source page.

let
    // Define a mapping from your desired column names to 
    // a list of candidate names the source might use
    ColumnMap = [
        Country = {"Country", "Nation", "Economy", "Country/Territory"},
        GDP_USD = {"GDP (USD bn)", "GDP USD", "GDP (Millions of US Dollars)", "GDP"},
        YearOverYear = {"YoY Change", "% Change", "Annual Change", "Growth %"}
    ],
    
    // Source data from scraping pipeline
    RawTable = /* result of scraping pipeline */,
    SourceColumns = Table.ColumnNames(RawTable),
    
    // Function: find best matching source column for a list of candidates
    FindColumn = (candidates as list) as text =>
    let
        Matches = List.Select(
            candidates,
            (c) => List.Contains(SourceColumns, c)
        ),
        // Try case-insensitive match if exact fails
        FuzzyMatches = if List.Count(Matches) > 0 then Matches else
            List.Select(
                SourceColumns,
                (sc) => List.AnyTrue(
                    List.Transform(
                        candidates,
                        (c) => Text.Upper(sc) = Text.Upper(c)
                    )
                )
            )
    in
        if List.Count(FuzzyMatches) > 0 then FuzzyMatches{0}
        else error Error.Record(
            "ColumnNotFound",
            "None of the candidate column names were found in source",
            Text.Combine(candidates, ", ")
        ),
    
    // Resolve each logical column to its source name
    CountryCol = FindColumn(ColumnMap[Country]),
    GdpCol = FindColumn(ColumnMap[GDP_USD]),
    YoyCol = FindColumn(ColumnMap[YearOverYear]),
    
    // Select and rename in one operation
    Selected = Table.SelectColumns(RawTable, {CountryCol, GdpCol, YoyCol}),
    Renamed = Table.RenameColumns(Selected, {
        {CountryCol, "Country"},
        {GdpCol, "GDP_USD_Billions"},
        {YoyCol, "YoY_Change_Pct"}
    })
in
    Renamed

This pattern means that when the source page renames "YoY Change" to "Annual Change," your query continues working. You only need to update the ColumnMap if an entirely new name appears that isn't in any candidate list — and the error message will tell you exactly which logical column failed to resolve.

---

Building a Parameterized, Reusable Scraping Function

Now let's compose everything into a proper reusable function. The goal is a single function that accepts a URL, a table identifier (class name or caption), and a column map, and returns a clean, typed table. Building this kind of function is what separates one-off queries from professional, reusable M function libraries.

let
    ScrapeHtmlTable = (
        pageUrl as text,
        tableClass as text,
        optional headerRows as nullable number,
        optional useBrowserRender as nullable logical
    ) as table =>
    let
        // Default parameter values
        HeaderRowCount = if headerRows = null then 1 else headerRows,
        UseBrowser = if useBrowserRender = null then false else useBrowserRender,
        
        // Fetch page content
        RawHtml = if UseBrowser 
                  then Web.BrowserContents(pageUrl)
                  else Web.Contents(pageUrl, [
                      Headers = [
                          #"User-Agent" = "Mozilla/5.0 (compatible; PowerQuery/1.0)"
                      ]
                  ]),
        
        // Parse the page
        ParsedPage = Web.Page(RawHtml),
        
        // Find target table(s)
        MatchingTables = Table.SelectRows(
            ParsedPage,
            each Text.Contains([ClassName], tableClass) or 
                 Text.Contains([Caption], tableClass)
        ),
        
        // Validate match count
        MatchCount = Table.RowCount(MatchingTables),
        
        RawTable = if MatchCount = 0 then
            error Error.Record(
                "TableNotFound",
                "No table matching '" & tableClass & "' found",
                pageUrl
            )
        else
            // Take the table with the most rows when multiple match
            (Table.First(
                Table.Sort(
                    Table.AddColumn(
                        MatchingTables, 
                        "_RowCount", 
                        each Table.RowCount([Data])
                    ),
                    {"_RowCount", Order.Descending}
                )
            ))[Data],
        
        // Handle multi-level headers
        AllRows = Table.ToRows(RawTable),
        
        FinalTable = if HeaderRowCount = 1 then
            Table.PromoteHeaders(RawTable, [PromoteAllScalars = true])
        else
        let
            // Forward-fill and concatenate header rows
            HeaderRows = List.FirstN(AllRows, HeaderRowCount),
            
            // Forward-fill each header row independently
            FillRow = (row as list) as list =>
                List.Accumulate(
                    List.Positions(row),
                    {},
                    (state, i) =>
                        let
                            Val = row{i},
                            Prev = if List.Count(state) = 0 then "" else List.Last(state),
                            Filled = if Val = null or Val = "" then Prev else Val
                        in
                            state & {Filled}
                ),
            
            FilledHeaders = List.Transform(HeaderRows, FillRow),
            
            // Build composite column names
            ColCount = List.Count(FilledHeaders{0}),
            CompositeNames = List.Transform(
                List.Numbers(0, ColCount),
                (i) =>
                    Text.Combine(
                        List.Select(
                            List.Transform(FilledHeaders, (h) => h{i}),
                            each _ <> null and _ <> ""
                        ),
                        " | "
                    )
            ),
            
            // Build data table from remaining rows
            DataRows = List.Skip(AllRows, HeaderRowCount),
            Built = Table.FromRows(DataRows, CompositeNames)
        in
            Built,
        
        // Add metadata about the extraction
        WithMeta = Value.ReplaceType(
            FinalTable,
            Value.Type(FinalTable) meta [
                Source = pageUrl,
                ExtractedAt = DateTime.LocalNow(),
                TableClass = tableClass
            ]
        )
    in
        WithMeta
in
    ScrapeHtmlTable

Tip: The metadata attachment at the end (Value.ReplaceType with a meta record) lets downstream queries or validation layers inspect how a table was produced without modifying its shape. This pairs well with the Mastering M Language Metadata patterns for building self-documenting pipelines.

---

Hands-On Exercise: Scraping a Real Financial Data Table

Let's build a complete pipeline using everything from this lesson. We'll target the Wikipedia page for S&P 500 companies, which has a well-structured but occasionally evolving table.

Your task: Build a pipeline that:

1. Extracts the S&P 500 constituent table
2. Selects the Symbol, Security, GICS Sector, and Date added columns using resilient semantic matching
3. Parses the Date added column to a proper Date type
4. Adds a metadata annotation to the output table
5. Returns an error with useful context if any expected column is missing

let
    // Step 1: Define target and parameters
    TargetUrl = "https://en.wikipedia.org/wiki/List_of_S%26P_500_companies",
    TargetClass = "wikitable",
    
    // Step 2: Invoke the ScrapeHtmlTable function
    // (Assumes ScrapeHtmlTable is defined as a separate query in your model)
    RawExtract = ScrapeHtmlTable(TargetUrl, TargetClass, 1, false),
    
    // Step 3: Resilient column selection
    SourceCols = Table.ColumnNames(RawExtract),
    
    FindBestColumn = (candidates as list) as text =>
    let
        ExactMatch = List.Select(candidates, each List.Contains(SourceCols, _)),
        CaseMatch = if List.Count(ExactMatch) > 0 then ExactMatch else
            List.Select(
                SourceCols,
                (sc) => List.AnyTrue(List.Transform(candidates, (c) => Text.Upper(sc) = Text.Upper(c)))
            )
    in
        if List.Count(CaseMatch) > 0 then CaseMatch{0}
        else error Error.Record("MissingColumn", "Expected column not found", Text.Combine(candidates, " / ")),
    
    SymbolCol  = FindBestColumn({"Symbol", "Ticker", "Ticker Symbol"}),
    NameCol    = FindBestColumn({"Security", "Company", "Company Name", "Name"}),
    SectorCol  = FindBestColumn({"GICS Sector", "Sector", "Industry Sector"}),
    DateCol    = FindBestColumn({"Date added", "Date Added", "Added", "Inclusion Date"}),
    
    // Step 4: Select and rename
    Selected = Table.SelectColumns(RawExtract, {SymbolCol, NameCol, SectorCol, DateCol}),
    Renamed = Table.RenameColumns(Selected, {
        {SymbolCol, "Ticker"},
        {NameCol, "CompanyName"},
        {SectorCol, "Sector"},
        {DateCol, "DateAdded_Raw"}
    }),
    
    // Step 5: Parse the date column safely
    WithDate = Table.AddColumn(
        Renamed,
        "DateAdded",
        each 
            let
                Raw = [DateAdded_Raw],
                Parsed = try Date.From(Raw) otherwise null
            in
                Parsed,
        type nullable date
    ),
    
    // Step 6: Drop the raw string column, keep the typed date
    Cleaned = Table.RemoveColumns(WithDate, {"DateAdded_Raw"}),
    
    // Step 7: Filter out any rows where Ticker is blank (often footnote rows)
    Filtered = Table.SelectRows(Cleaned, each [Ticker] <> null and [Ticker] <> ""),
    
    // Step 8: Type the table
    Typed = Table.TransformColumnTypes(Filtered, {
        {"Ticker", type text},
        {"CompanyName", type text},
        {"Sector", type text},
        {"DateAdded", type nullable date}
    })
in
    Typed

To extend this exercise: try wrapping the entire pipeline in error handling using try...otherwise so that a network failure or page structure change produces a one-row error table rather than a query failure, allowing other queries that depend on this one to still refresh.

---

Common Mistakes & Troubleshooting

"Expression.Error: The key didn't match any rows"

This appears when you use {0} positional indexing on an empty table — meaning Web.Page found no tables matching your filter. Causes:

• The CSS class name changed (site redesign)
• The page requires JavaScript rendering (use Web.BrowserContents)
• Your Web.Contents request was blocked and returned an error page or CAPTCHA

Fix: Always wrap table access with a row count check, as shown in the navigation section above.

Tables with Identical Class Names

When multiple tables on the page share the same class, Text.Contains on ClassName returns all of them. The heuristic of choosing the one with the most rows usually works for data tables vs navigation tables, but a better approach is to also filter by minimum row count:

Table.SelectRows(
    MatchingTables,
    each Table.RowCount([Data]) > 10  // Skip navigation tables with few rows
)

Column Count Inconsistency Across Rows

When Web.Page parses a table with rowspan attributes (cells that span multiple rows), later rows may have fewer columns than the header row. This produces null columns or rows being silently dropped. You'll notice it when your output has fewer rows than expected.

Detection:

let
    RowLengths = List.Transform(Table.ToRows(RawTable), List.Count),
    MaxLength = List.Max(RowLengths),
    InconsistentRows = List.Select(RowLengths, each _ < MaxLength)
in
    List.Count(InconsistentRows)  // Non-zero means rowspan issues

Fix: For severe rowspan problems, fall back to the Source column raw HTML parsing approach and implement explicit rowspan expansion logic.

Refresh Fails in Power BI Service but Works in Desktop

The most common cause: Web.BrowserContents isn't supported in that gateway configuration, or Web.Contents is being blocked by the gateway's outbound firewall. Less commonly, the site uses IP-based rate limiting and the gateway's IP triggers it.

Fix: Check gateway data source settings in Power BI Admin. For rate limiting, add a [ManualStatusHandling = {429}] option to Web.Contents and inspect the response:

Response = Web.Contents(PageUrl, [ManualStatusHandling = {429, 403}]),
ResponseCode = Value.Metadata(Response)[Response.Status],
Content = if ResponseCode = 429 then 
    error Error.Record("RateLimited", "Source returned 429 Too Many Requests", PageUrl)
else if ResponseCode = 403 then
    error Error.Record("Blocked", "Access denied — check User-Agent or authentication", PageUrl)
else 
    Response

Warning: Rate limiting your scraping requests responsibly isn't just polite — some jurisdictions have legal frameworks around automated web access. Always check a site's robots.txt file and terms of service before building a production scraping pipeline. Where an API exists, use it instead.

Headers Appearing as Data Rows

This happens when Web.Page fails to detect <thead> vs <tbody> properly — which is common when the original HTML doesn't use <thead> at all (still shockingly common on older sites). You'll see your header row as the first data row with values like "Country", "GDP", etc.

Detection and Fix:

let
    FirstRow = Table.First(RawTable),
    // Check if the first row looks like headers (no numeric values)
    IsHeaderRow = List.AllTrue(
        List.Transform(
            Record.ToList(FirstRow),
            each try (Number.From(_) is number) otherwise true
        )
    ),
    Promoted = if IsHeaderRow then
        Table.PromoteHeaders(RawTable, [PromoteAllScalars = true])
    else
        RawTable
in
    Promoted

---

Performance Considerations

Web scraping in Power Query carries specific performance implications you should understand before deploying to a scheduled environment.

Query folding doesn't apply. Web.Contents and Web.Page are data source steps that cannot fold any downstream transformations back to the source. Every filter, transformation, and type conversion happens in the M engine after the full page is fetched. This is covered in depth in M Language Performance Patterns and Anti-Patterns: Optimize Power Query for Speed, but the key implication here is: don't try to use the web source as a starting point for heavy computation. Fetch, normalize, and cache.

Buffer the scraped table early. If multiple downstream steps reference your scraped table, M's lazy evaluation may re-fetch the URL multiple times. Use Table.Buffer immediately after extraction to cache the result in memory:

Buffered = Table.Buffer(RawExtract),

This is one of the most impactful single-line optimizations for scraping pipelines. See Implementing Custom Table.Buffer and In-Memory Caching Strategies in M for a thorough treatment of when and how to apply buffering.

Schedule refresh frequency carefully. Scraping the same URL on every report load is wasteful and may trigger rate limiting. Consider whether incremental refresh is appropriate — if the data changes daily, a daily scheduled refresh is sufficient.

---

Summary & Next Steps

You now have a complete toolkit for HTML table scraping in Power Query M that goes well beyond the default connector experience:

• Precise navigation using CSS class and caption matching instead of brittle positional indexing
• Multi-level header handling with forward-fill accumulators for colspan tables
• Raw HTML fallback parsing for cases where Web.Page produces broken structure
• Resilient semantic column selection that survives column additions and renames
• JavaScript-rendered table access via Web.BrowserContents
• Production-grade validation with structured error records and metadata annotation
• A composable, parameterized scraping function you can call across multiple queries

The logical next steps from here depend on your use case. If you're building a multi-source scraping system where dozens of pages feed into a single model, the Building Multi-Stage ETL Pipelines in Power Query M lesson will help you orchestrate those pipelines cleanly. If your scraped data needs to be validated against a schema contract before it enters your model, the Table.Schema Validation and Type Enforcement Pipelines lesson covers exactly that. And if the site you're targeting eventually exposes a JSON API — which you should always check first — the patterns in Advanced JSON and XML Processing in Power Query M Language will serve you better than anything in this lesson.

The goal is resilience. A scraping pipeline that works today and breaks silently in three months isn't an asset — it's a liability. Everything in this lesson is designed to ensure that when something changes on the source, your pipeline either adapts gracefully or fails loudly with enough context to fix it quickly.