Web and Online Data Sources
Learn how to connect Power BI Desktop to web pages, online files, public datasets, and web APIs. Understand URLs, structured web data, JSON responses, authentication, and data refresh considerations.
1. Introduction to Web Data Sources
Web data sources provide information through websites, downloadable files, and online services. Power BI can connect to supported web sources and import data that can be transformed, modeled, and visualized.
Common Online Sources
- HTML tables published on web pages.
- CSV and Excel files hosted online.
- JSON files and API responses.
- Cloud-hosted business datasets.
- Supported online services and connectors.
Visual 1: Online data is connected, prepared, modeled, and used in reports.
2. Connecting to a Web Page
The Web connector can retrieve supported web content. If a page contains recognizable tables, Power BI may display them in the Navigator window. The available results depend on the page structure and access requirements.
Steps to Connect
- Open Power BI Desktop.
- Select Home → Get Data → Web.
- Enter the URL of the intended web resource.
- Choose the appropriate connection option if prompted.
- Review the available tables or data objects in Navigator.
- Select a suitable item and choose Transform Data or Load.
- Inspect column names, data types, and sample rows.
Visual 2: The typical workflow for connecting to a web resource.
3. Understanding URLs and Web Content
A URL identifies a resource on the internet. Before connecting, verify that the address points to the intended page, file, or API endpoint.
Example URL Structure
- https:// identifies the secure web protocol.
- example.com identifies the host.
- /data/sales.csv identifies the resource path.
| Resource Type | Typical Content | What to Check |
|---|---|---|
| HTML page | Page text and possibly tables | Whether the required data is exposed in a supported structure |
| CSV file | Rows and columns of delimited values | Delimiter, encoding, headers, and file availability |
| JSON endpoint | Objects, arrays, and properties | Response structure, authentication, and pagination |
| Excel file | Worksheets and tables | File access, workbook structure, and correct URL |
4. Introduction to Web APIs
An Application Programming Interface (API) allows software applications to request information or perform supported operations. Many APIs return data in JSON format. Power BI can consume suitable API responses when the endpoint, authentication method, and response format are supported.
Example API Request Concept
This example represents a hypothetical endpoint. It is not a live service and will not return real sales data.
Example JSON Response
In Power Query, JSON data may appear as records and lists. These structures can be expanded into a table so that fields such as orderId, region, and amount become columns.
Visual 3: Nested JSON objects can be expanded into rows and columns.
5. Loading and Transforming Online Data
Online data often requires preparation before it is suitable for reporting. Use Power Query Editor to review the structure and apply repeatable transformation steps.
- Remove unnecessary columns.
- Promote the correct row to column headers when required.
- Set accurate data types for text, dates, whole numbers, and decimals.
- Expand records or lists in JSON responses when appropriate.
- Filter unwanted rows and handle missing values.
- Rename columns using clear business terms.
- Check the final table for errors and unexpected values.
Example: Preparing an Online Sales Table
| Order ID | Order Date | Region | Amount |
|---|---|---|---|
| 1001 | 2026-01-05 | West | 2500 |
| 1002 | 2026-01-06 | North | 1800 |
| 1003 | 2026-01-07 | South | 3200 |
The Order Date column should use a date type, Amount should use a suitable numeric type, and Order ID should remain a stable identifier.
6. Authentication and Online Access
Some online sources are public, while others require a supported authentication method. The correct method depends on the service and connector.
| Authentication Method | Typical Use | Important Consideration |
|---|---|---|
| Anonymous | Public resources that do not require sign-in | Use only when the resource is intended to be public. |
| Organizational account | Supported work or school services | Access depends on account permissions and service support. |
| Basic authentication | Services that explicitly support a username and password | Use secure connections and follow organizational policies. |
| Web API credentials | APIs that support API key authentication through the connector | Check the provider's security and credential requirements. |
In Power BI Desktop, review the data source credentials settings when authentication fails. In the Power BI Service, refresh may also require credentials to be configured for the published semantic model.
7. Refreshing Online Data
Imported online data represents the content retrieved during a refresh. If the source changes, the report does not automatically reflect every change unless a refresh or another supported update mechanism occurs.
Visual 4: Refresh retrieves online data and updates the imported model when successful.
Refresh Checklist
- Confirm that the source URL is still valid.
- Verify credentials and permissions.
- Check whether the endpoint is accessible from the Power BI Service.
- Review refresh history for errors.
- Check source limits, API throttling, and pagination behavior.
- Test refresh after changing credentials or query steps.
8. Common Problems and Solutions
| Problem | Possible Cause | Suggested Action |
|---|---|---|
| Access denied | Missing credentials or insufficient permissions | Verify access and configure supported credentials. |
| No tables appear | The page does not expose recognizable tables | Check the source format or use a suitable file or API endpoint. |
| Data format error | Unexpected HTML, JSON, or file structure | Inspect the response and adjust the query steps. |
| Refresh failure | Expired credentials, changed URL, or unavailable service | Review credentials, URL, service access, and refresh history. |
| Missing records | Pagination, filters, or API limits | Review the provider's documentation and confirm all pages are retrieved. |
| Slow response | Large response or slow source service | Reduce unnecessary data and optimize query steps where possible. |
9. Practical Exercise
Exercise: Import and Prepare Online Sales Data
Scenario: A sales analyst needs to analyze regional sales from an online data source.
- Identify a trusted web resource that provides a suitable sample dataset.
- Use Get Data to connect to the resource.
- Inspect the available tables or response objects.
- Load the relevant data into Power Query Editor.
- Rename columns and set appropriate data types.
- Remove irrelevant fields and check for missing values.
- Load the prepared table and create a regional sales chart.
- Document any access or refresh requirements.
Expected outcome: A clean table that can support a simple regional sales report.
10. Knowledge Check
Question 1: Which Power BI feature is commonly used to connect to a supported web resource?
A. Get Data → Web
B. Format Painter
C. Manage Relationships only
D. Page Navigator
Question 2: What is JSON commonly used to represent?
A. Only images
B. Structured objects and arrays
C. Page formatting only
D. Power BI themes only
Question 3: Why may an online source require credentials?
A. To change chart colors
B. To grant authorized access to protected data
C. To create a measure automatically
D. To resize a report page
Question 4: What should you check when an API returns incomplete data?
A. Report background color
B. Visual title size
C. Pagination, filters, and API limits
D. Card border radius
View Answers
- A — Get Data → Web.
- B — Structured objects and arrays.
- B — To grant authorized access to protected data.
- C — Pagination, filters, and API limits.