How to Connect to API in Power BI: Step-by-Step Guide

To connect Power BI to an API, use the Get Data > Web option and enter the API URL. Then, use Power Query to transform and load the API data into your report.

Syntax

Power BI connects to APIs using the Web connector with this basic syntax:

• Web.Contents(url, options): Fetches data from the API URL.
• url: The API endpoint as a string.
• options: Optional record to specify headers, query parameters, or HTTP method.

This is used inside Power Query's M language to get and transform API data.

powerquery

let
    Source = Web.Contents("https://api.example.com/data", [Headers=[Authorization="Bearer YOUR_TOKEN"]])
in
    Source

Example

This example shows how to connect to a public API that returns JSON data and load it into Power BI.

powerquery

let
    Source = Web.Contents("https://jsonplaceholder.typicode.com/posts"),
    JsonResponse = Json.Document(Source),
    TableFromJson = Table.FromList(JsonResponse, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedTable = Table.ExpandRecordColumn(TableFromJson, "Column1", {"userId", "id", "title", "body"})
in
    ExpandedTable

Output

A table with columns: userId, id, title, body showing posts data from the API

Common Pitfalls

• Missing authentication: Many APIs require tokens or keys in headers; forgetting this causes errors.
• Incorrect URL or parameters: Typos or wrong query strings lead to no data or errors.
• Not parsing JSON: API data often comes as JSON; you must use Json.Document() to convert it.
• Ignoring rate limits: APIs may limit calls; too many requests can block access.

powerquery

/* Wrong: Missing JSON parsing */
let
    Source = Web.Contents("https://jsonplaceholder.typicode.com/posts")
in
    Source

/* Right: Parse JSON to table */
let
    Source = Web.Contents("https://jsonplaceholder.typicode.com/posts"),
    JsonResponse = Json.Document(Source)
in
    JsonResponse

Quick Reference

Step	Description
1. Get Data > Web	Open Power BI Desktop and choose Web connector.
2. Enter API URL	Paste the API endpoint URL.
3. Add Headers	Use Power Query to add authentication headers if needed.
4. Parse JSON	Use Json.Document() to convert API response to table.
5. Transform Data	Clean and shape data as needed.
6. Load Data	Load the data into Power BI report.

Key Takeaways

Use Power BI's Web connector with Web.Contents() to fetch API data.

Always parse JSON responses with Json.Document() before loading.

Include authentication headers if the API requires them.

Check API URL and parameters carefully to avoid errors.

Transform and clean API data in Power Query before loading.