How to Scrape Web Data Without Writing Code Using Power BI

Traditional web data collection pipelines often begin with a small Python script and gradually become a maintenance burden. A workflow built with BeautifulSoup, Selenium, or Playwright may perform reliably until the source website changes its DOM structure, renames a CSS class, moves a table into a nested container, or replaces server-rendered content with JavaScript components. Once selectors break, engineers must inspect the page again, update the parsing logic, retest the workflow, and redeploy the script.

This overhead becomes more significant when multiple websites, reporting schedules, authentication flows, and downstream datasets are involved. The technical challenge is not always extracting the data once. It is keeping the pipeline operational as page structures evolve.

Power BI offers a different approach. Beyond dashboards and data visualization, its Power Query engine can function as a no-code web data ingestion layer. Using the Web connector, table detection, and example-based data extraction, analysts can identify relevant page content, transform it, and load it into a structured analytical model without maintaining a separate scraping application.

Instead of requiring manually written XPath expressions or CSS selectors for every field, Power BI can evaluate repeated patterns in the page’s HTML and infer how related values should be organized into rows and columns. Its heuristic extraction capabilities and, in features such as Web by Example, machine-learning-assisted pattern recognition help convert semi-structured web content into relational tables that can be filtered, joined, refreshed, and reused across reports.

This does not eliminate every challenge associated with web data collection, particularly for heavily dynamic, protected, or interaction-driven websites. However, for accessible pages with consistent content patterns, Power BI can replace a surprising amount of custom parsing code. The first step is understanding how its web connector discovers and interprets data inside an HTML page.

The Core Features: How Power BI Extracts Web Data

The Web Dynamic Data Connector: Your Portal to the Web

Power BI’s Web connector is an ingestion interface backed by the Power Query engine. For standard pages, Web.Contents issues an HTTP GET request and returns the response body as a binary payload; specifying a request body changes the operation to POST. Query parameters, relative paths, timeouts, caching behavior, status-code handling, and custom HTTP headers can all be controlled through Power Query M options.

Authentication is managed through Power BI’s credential layer rather than embedded directly in the query. With Basic authentication, the user supplies a username and password through the connector dialog, and Power Query applies those credentials to the configured URL scope. Additional request headers can be passed through the Headers record in Web.Contents, while API keys should be stored through the connector’s credential mechanism instead of hard-coded into M.

Once the response is retrieved, Power Query interprets the HTML document as a structured source. Web.Page decomposes the document into its constituent structures, while Html.Table evaluates CSS selectors against the HTML and converts matching nodes into columns and relational rows. For JavaScript-rendered pages, Web.BrowserContents can return browser-rendered HTML and wait for a specified CSS selector before capturing the page state.

The result is a repeatable ingestion path:

HTTP response → rendered HTML → DOM-aligned selectors → typed Power Query table

“Extract Table Using Examples”: Power BI’s AI Secret Weapon

“Extract Table Using Examples,” presented in the interface as Add table using examples, is designed for pages where useful data exists in repeated cards, lists, product blocks, or nested containers rather than conventional HTML tables.

The user supplies one or more representative values for each desired column for example, two product names and their corresponding prices. Power Query then searches the page for matching values and applies its smart data-extraction algorithms to identify the repeated structural pattern surrounding them. Microsoft does not publish the exact model architecture, but the generated extraction logic is consistent with selector-based schema inference: the engine evaluates candidate HTML paths, tag relationships, attributes, CSS classes, sibling repetition, and common parent containers to determine which nodes represent fields and which repeated container represents a row.

From those examples, Power Query effectively infers three components:

• Column selectors: DOM paths or CSS selectors that locate each requested attribute.
• Row selector: The repeated parent structure that defines one relational record.
• Schema mapping: The association between extracted nodes and named Power Query columns.

Suppose the page contains repeated product cards:

<div class="product-card">
    <span class="product-name">Laptop A</span>
    <span class="price">$899</span>
</div>

After receiving two or three valid examples, Power Query can infer that .product-card defines the relational row boundary, while .product-name and .price represent column-level selectors. It then applies that pattern across all structurally equivalent nodes, producing a complete table without requiring the analyst to write or maintain the selectors manually.

This is why the feature can extract structured datasets from visually consistent but semantically untidy HTML. The analyst provides the expected output; Power Query derives the page-to-table mapping. The next step is to apply this mechanism to a live webpage and inspect the M query Power BI generates behind the interface.

Step-by-Step: Scraping Your First Website with Power BI

This walkthrough extracts product names and prices from a public e-commerce page. The source uses repeated product cards rather than a clean HTML <table>, making it suitable for testing Power BI’s example-based extraction capability.

Use the following Microsoft Store page:

https://www.microsoft.com/store/top-paid/games/xbox?category=classics

Microsoft uses this page in its official Power Query documentation to demonstrate Add table using examples. The page structure and individual products may change, so use the product names and prices currently visible when you run the exercise.

Step 1: Connecting to the Target URL

1. Open the Web connector

1. Open Power BI Desktop.
2. Create a blank report.
3. Select the Home tab.
4. Select Get data.
5. Select Web.

When Web is not displayed in the short connector list:

1. Select Get data.
2. Select More.
3. Open the Other category.
4. Select Web.
5. Select Connect.

Power BI opens the From Web dialog. This connector retrieves the webpage and passes its content to the Power Query engine for inspection.

2. Enter the target URL

In the From Web dialog:

1. Keep the Basic tab selected.
2. Paste the target URL into the URL field.
3. Select OK.

Use Basic here to mean the basic URL-entry mode. It is separate from the Basic authentication method presented later.

For endpoints that require query-string construction, request headers, multiple URL components, or a longer timeout, use the Advanced tab instead. For this exercise, the standard URL field is sufficient.

3. Configure access permissions

The first time Power BI accesses a domain, it opens an authentication dialog.

For the public Microsoft Store page:

1. Select Anonymous.
2. Select the URL scope to which the credentials should apply.
3. Prefer the narrowest practical scope.
4. Select Connect.

Anonymous access is appropriate when the webpage does not require credentials.

For a website protected by HTTP Basic authentication:

1. Select Basic.
2. Enter the assigned username.
3. Enter the password.
4. Select the appropriate URL scope.
5. Select Connect.

Power BI stores these credentials through its data-source permission layer. Do not place usernames or passwords directly in Power Query M unless a controlled custom-connector design specifically requires it.

The Web connector currently supports authentication methods including Anonymous, Windows, Basic, Web API, and Organizational account. The selected URL level determines which paths inherit that authentication configuration.

4. Reset incorrect credentials when necessary

A common development issue is testing a URL with the wrong authentication mode and then repeatedly receiving the same error.

To reset the stored configuration:

1. Select File.
2. Select Options and settings.
3. Select Data source settings.
4. Find the target domain.
5. Select Edit Permissions to change the authentication method.

Alternatively:

1. Select the source.
2. Select Clear Permissions.
3. Reconnect through Get data > Web.
4. Enter the correct credentials.

5. Inspect the Navigator output

After Power BI retrieves the page, it opens the Navigator dialog.

Power BI may display:

• Detected HTML tables
• Suggested document structures
• A webpage preview
• No usable table at all

Do not select an unrelated detected table simply because one appears.

Switch between:

• Table View, which previews detected tabular objects
• Web View, which displays the webpage and highlights structures Power BI has identified

A conventional Wikipedia table often appears immediately as a selectable table. An e-commerce page built from nested <div>, <article>, or component-based product cards may not. That is the use case for example-driven schema inference.

Step 2: Using AI to Map Out Non-Standard Layouts

1. Open the example-based extraction interface

In the lower-left corner of the Navigator dialog:

1. Select Add table using examples.
2. Wait for the interactive webpage preview to load.

The window contains two primary areas:

• A rendered preview of the source page
• An empty table where you define the expected schema

You are not manually selecting CSS classes or writing XPath expressions. You are supplying representative output values.

2. Define the first output column

Start with the product-name field.

1. Select the first empty column header.
2. Rename it Product Name.
3. Locate a visible product name in the webpage preview.
4. Enter that exact value in the first row.
5. Enter a second visible product name in the next row.
6. Add a third example when the initial inference is incomplete.

Use exact text from the page. Preserve punctuation and spacing during training.

For example:

As you enter examples, Power Query searches the page for matching values and attempts to identify the repeated HTML structure that contains them.

Microsoft describes this process as using smart data-extraction algorithms to locate other page values that match the supplied examples.

3. Add the price column

Create the second field.

1. Select the next empty column.
2. Rename it Price Raw.
3. In row one, enter the price belonging to the first product.
4. In row two, enter the price belonging to the second product.
5. Enter a third product-price pair when required.

Maintain row alignment.

Do not enter three unrelated product names and three unrelated prices. Each row must represent one logical product record:

The examples give the engine both column-level and row-level evidence.

Conceptually, Power Query must infer:

• Which repeated container represents one product
• Which descendant node contains the product name
• Which descendant node contains the price
• How those nodes should be mapped into relational columns
• Which repeated containers should become additional rows

4. Wait for the inferred table to populate

After two or three examples, Power Query should begin filling the remaining rows automatically.

Review the inferred result before accepting it.

Check for the following:

• Product names belong to the correct prices.
• One webpage card produces one relational row.
• Navigation labels are not included as products.
• Promotional text is not being interpreted as a price.
• The same product is not repeated unexpectedly.
• Empty cards or placeholders are not producing null-heavy rows.
• The output contains more rows than the supplied examples.

The goal is not merely to see additional values. The goal is to confirm that the inferred row boundary is correct.

5. Validate the mapping against the webpage

Use a structured validation pass.

Check the first row

Compare the first inferred row with the first visible product card.

Check a middle row

Scroll through the webpage preview. Select a product that was not used as an example. Confirm that its name and price appear together in the generated table.

Check the last available row

Inspect the bottom of the preview. Confirm that the extraction has not stopped after the training examples.

Check for false positives

Search the generated table for values taken from:

• Page navigation
• Category filters
• Promotional banners
• Footer links
• Cart controls
• Previous-price labels
• Subscription messages

A valid extraction should consistently represent the intended product-card structure.

6. Correct an inaccurate inference

When the generated schema is wrong, do not immediately accept it and repair everything downstream.

Improve the training examples first.

Use one or more of these techniques:

1. Enter a third example from a structurally different position on the page.
2. Replace an ambiguous sample with a unique product name.
3. Add another column that helps define the row boundary.
4. Remove an example that occurs in multiple locations.
5. Use the current selling price rather than an unrelated crossed-out price.
6. Confirm that every supplied value exists in the rendered preview.

For example, adding Rating, Category, or Product URL can help the engine distinguish a product card from a promotional component when those fields are consistently present.

7. Commit the inferred schema

Once the preview is correct:

1. Select OK.
2. Return to the Navigator dialog if prompted.
3. Select the newly created custom table.
4. Select Transform Data.

Do not select Load yet.

Selecting Transform Data moves the inferred table into Power Query Editor, where the raw web output can be normalized before it enters the semantic model. Microsoft explicitly supports applying additional shaping and transformation steps after example-based extraction.

Step 3: Cleaning Messy Web Data with Power Query

The extracted table is structurally useful but not necessarily model-ready.

Typical web values include:

• Currency symbols
• Thousands separators
• Non-breaking spaces
• Promotional prefixes
• Repeated headings
• Empty product cards
• Mixed text and numeric values
• Incorrectly inferred data types

Treat Power Query Editor as the staging and normalization layer.

1. Rename the query

In the Query Settings pane:

1. Find the Name field.
2. Rename the query to something explicit, such as:

stg_Web_ProductPrices

A staging-oriented name makes the query’s role clear. It also separates raw ingestion from later semantic-model tables.

2. Rename columns predictably

Rename the extracted columns:

• Product Name
• Price Raw

Use stable, descriptive names. Avoid names such as Column1, Column2, or Data.

To rename a column:

1. Double-click the column header.
2. Enter the new name.
3. Press Enter.

3. Remove leading and trailing whitespace

Select Product Name.

Then:

1. Open the Transform tab.
2. Select Format.
3. Select Trim.

Next:

1. Keep the column selected.
2. Select Transform > Format > Clean.

Trim removes unnecessary leading and trailing spaces. Clean removes non-printable characters that may have entered through the HTML response.

Apply the same steps to Price Raw when the values contain hidden spaces.

4. Remove currency symbols through the GUI

Select the Price Raw column.

Then:

1. Select Transform.
2. Select Replace Values.
3. In Value to find, enter the currency symbol, such as $.
4. Leave Replace with empty.
5. Select OK.

For values such as $1,299.99, repeat the operation:

1. Select Transform > Replace Values.
2. Enter , in Value to find.
3. Leave Replace with empty.
4. Select OK.

The result should change from:

$1,299.99

to:

1299.99

When the page contains prefixes such as From $9.99:

1. Select Transform > Replace Values.
2. Replace From with an empty value.
3. Apply Transform > Format > Trim again.

When the source uses non-breaking spaces between the currency symbol and value, copy the visible spacing directly from one cell into Value to find, replace it with nothing, and trim the result.

5. Preserve the original value during development

For traceability, keep the raw price and create a cleaned copy.

1. Right-click Price Raw.
2. Select Duplicate Column.
3. Rename the copy Price.
4. Apply symbol removal and type conversion to Price.
5. Retain Price Raw until validation is complete.

This provides a direct comparison between the source text and the normalized numeric value.

6. Enforce a strict currency data type

Select the cleaned Price column.

Then:

1. Right-click the column header.
2. Select Change Type.
3. Select Fixed Decimal Number.

Use Fixed Decimal Number for monetary values that require predictable decimal precision.

Power BI’s Fixed Decimal Number type stores four digits to the right of the decimal separator and corresponds conceptually to a decimal or currency-oriented numeric representation. Unlike floating-point Decimal Number values, it reduces the risk of approximation errors in monetary aggregation.

7. Use locale-aware conversion when necessary

A price such as 1,299.99 and a price such as 1.299,99 use different thousands and decimal conventions.

When the source locale differs from the workstation locale:

1. Right-click Price.
2. Select Change Type.
3. Select Using Locale.
4. Choose Fixed Decimal Number.
5. Select the locale that matches the source page.
6. Select OK.

Examples:

• $1,299.99 → English (United States)
• €1.299,99 → German (Germany)
• £1,299.99 → English (United Kingdom)

Power Query Desktop otherwise interprets text-to-number conversions using its configured regional settings. Locale-aware conversion prevents valid numeric strings from becoming errors or being parsed incorrectly.

8. Use Whole Number for integer fields

Fields such as the following should usually use Whole Number:

• Review count
• Stock quantity
• Product rank
• Number of ratings
• Units available

To enforce the type:

1. Select the column.
2. Right-click the column header.
3. Select Change Type.
4. Select Whole Number.

Do not use Whole Number for prices containing cents. It removes the fractional component or causes conversion errors, depending on the input.

9. Remove null noise

Open the filter dropdown on Product Name.

Then:

1. Clear the checkbox for null.
2. Clear the checkbox for blank values.
3. Select OK.

Repeat the process for Price when every valid product is expected to have a price.

Be careful with genuinely nullable fields. A missing discount, rating, or previous price may be valid. Filter nulls only when the null indicates a failed or irrelevant row.

10. Remove repeated header rows

Some web layouts inject labels such as Product Name, Name, Price, or Current Price into the extracted rows.

Open the Product Name filter:

1. Clear repeated heading values such as Product Name or Name.
2. Select OK.

For more controlled filtering:

1. Select the filter dropdown.
2. Choose Text Filters.
3. Select Does Not Equal.
4. Enter the repeated heading value.
5. Select OK.

Apply similar filtering to the price column when it contains strings such as Price, Current Price, or Starting at.

11. Remove error rows selectively

After changing Price to Fixed Decimal Number, conversion failures appear as Error.

Do not remove all errors without inspection.

First:

1. Select the Price column.
2. Open the Home tab.
3. Select Keep Rows.
4. Select Keep Errors.

Inspect the failed source values.

Determine whether the errors come from:

• Promotional text
• Unexpected currency symbols
• Empty strings
• Price ranges
• Unavailable products
• Locale mismatches
• Repeated header rows

Return to the previous step and extend the cleaning logic.

Once the transformation handles the expected formats:

1. Select the Price column.
2. Select Home > Remove Rows > Remove Errors only when the remaining errors represent invalid records.

12. Check column quality

In Power Query Editor:

1. Select the View tab.
2. Enable Column quality.
3. Enable Column distribution.
4. Enable Column profile.

Review the quality indicators for:

• Valid values
• Errors
• Empty values
• Distinct counts
• Unexpected duplicates

By default, profiling may be based on the first 1,000 rows. For a complete validation pass, change the profiling mode at the bottom of Power Query Editor to use the entire dataset.

13. Review the generated transformation pipeline

Open the Query Settings pane and inspect Applied Steps.

A clean pipeline may resemble:

Source
Extracted Table From Html
Changed Type
Renamed Columns
Duplicated Column
Replaced Value
Replaced Value1
Trimmed Text
Filtered Rows
Changed Type1

Rename important steps where useful:

• Extract_ProductCards
• Remove_CurrencySymbol
• Remove_ThousandsSeparator
• Filter_NullProducts
• Cast_Price_FixedDecimal

To rename a step:

1. Right-click the step.
2. Select Rename.
3. Enter a descriptive name.

This makes the transformation graph easier to debug when the source website changes.

14. Inspect the generated M code

Enable the formula bar:

1. Select View.
2. Enable Formula Bar.
3. Select each item under Applied Steps.
4. Inspect the generated M expression.

To view the complete query:

1. Select View.
2. Select Advanced Editor.

Although the workflow was created through the GUI, every operation is stored as Power Query M. This gives developers a reviewable and versionable representation of the extraction and transformation logic. Power BI exposes both individual step formulas and the full M query through the Formula Bar and Advanced Editor.

15. Load the validated dataset

Complete one final check:

• Product Name is Text.
• Price is Fixed Decimal Number.
• Invalid headings are removed.
• Null product rows are removed.
• Product-price pairs remain aligned.
• No unexplained conversion errors remain.
• The Applied Steps sequence is readable.

Then:

1. Select the Home tab.
2. Select Close & Apply.
3. Wait for the query to load into the Power BI semantic model.

The resulting table is now ready for measures, relationships, price comparisons, refresh testing, and downstream reporting.