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

  1. Get data → Web
  2. Enter the API base URL and options
  3. Power Query parses the response with Json.Document
  4. Is the result a list of records? Yes: Convert to table and expand columns. No: Drill into the field that holds the list.
  5. Set data types on the new columns
  6. 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
    Typed

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic