Connect to Web APIs
Power BI · Getting Data
Theory
The Web connector pulls data from a URL: a web page, a downloadable file, or a REST API that returns JSON or XML. In M the call is Web.Contents(url, options), and the result is passed to Json.Document (or Xml.Tables) to turn the response into lists and records. From there you convert the list to a table and expand the record columns into normal columns.
For example, an internal API at https://api.example.in/orders returns a JSON list of orders with id, region and amount. Power Query reads it as a List of Records; To Table followed by Expand gives a table with one row per order, ready to merge with the rest of the model. Keep the base URL fixed and pass changing parts through the RelativePath and Query options, which also helps scheduled refresh in the Service.
Why we use it
Some data never lands in a database: SaaS tools, public datasets, exchange rates. The Web connector lets you bring it into the same model and refresh it like any other source.
When to use it
Use it for APIs and online files that Power BI has no dedicated connector for. If a certified connector exists (for example for a SaaS product), prefer it. For APIs with paging or complex authentication, consider landing the data in a database first.
How it works
- Get data → Web
- Enter the API base URL and options
- Power Query parses the response with Json.Document
- Is the result a list of records? Yes: Convert to table and expand columns. No: Drill into the field that holds the list.
- Set data types on the new columns
- Load and refresh like any other table
Syntax
Home → Get data → Web → enter URL (Basic or Advanced)
M:
Web.Contents(url as text, optional options as record)
options: [RelativePath = "orders", Query = [region = "North"], Headers = [Accept = "application/json"]]
Json.Document(binary)Example
let
Source = Json.Document(
Web.Contents("https://api.example.in",
[RelativePath = "orders", Query = [year = "2024"]])),
AsTable = Table.FromRecords(Source),
Typed = Table.TransformColumnTypes(AsTable,
{{"id", Int64.Type}, {"region", type text}, {"amount", Int64.Type}})
in
TypedKey points
- Supports JSON, XML and OData responses
- Web.Contents + Json.Document is the core pattern
- Pass query parameters through Query and RelativePath options
- API keys belong in data source credentials, not hard-coded in M
- Useful for pulling in third-party or public datasets