OData and REST API connections in Power BI - Step-by-Step Guide

Introduction

This feature helps you bring data from online services into Power BI using OData or REST APIs. It solves the problem of connecting to live web data sources so you can analyze and visualize them easily.

When you want to get sales data from an online service that provides an OData feed.

When your marketing data is available through a REST API and you want to create reports in Power BI.

When you need to refresh your dashboard automatically with data from a web service.

When your company uses cloud services that expose data via OData or REST APIs.

When you want to combine web data with your local data for deeper insights.

Steps

Step 1: Click

- Home tab > Get data dropdown

The Get Data window opens showing data source options

Step 2: Select

- Get Data window > Online Services > OData Feed

The OData Feed connection dialog appears

Step 3: Type or paste

- OData Feed dialog > URL field

The URL of the OData service is entered

Step 4: Click

- OData Feed dialog > OK button

Power BI connects to the OData service and shows available tables

Step 5: Select

- Navigator pane

The chosen tables or data sets are highlighted for import

Step 6: Click

- Navigator pane > Load button

The selected data loads into Power BI for analysis

Step 7: For REST API, click

- Home tab > Get data dropdown > Web

The Web connection dialog opens to enter REST API URL

💡 Use advanced options to add headers or authentication if needed

Before vs After

Before

Power BI has no data loaded from the web service

After

Power BI shows tables and data imported from the OData or REST API source ready for reporting

Settings Reference

Authentication Method

📍 OData Feed or Web connection dialog

To securely connect to the data source using the correct credentials

Default: Anonymous

HTTP Request Headers

📍 Web connection dialog > Advanced options

To send extra information like API keys or tokens required by REST APIs

Default: None

Data Load Mode

📍 Navigator pane > Load dropdown

Choose to load data directly or open Power Query Editor to clean data first

Default: Load

Common Mistakes

Entering the wrong URL format for the OData or REST API endpoint

Power BI cannot connect if the URL is incorrect or incomplete

Copy the exact URL from the service documentation or test it in a browser first

Not setting authentication when the API requires it

Connection fails or returns no data without proper credentials

Use the Authentication Method setting and provide necessary credentials or tokens

Trying to load large data sets without filtering or transforming first

This can slow down Power BI or cause memory issues

Use Transform Data to filter or reduce data before loading

Summary

OData and REST API connections let you bring live web data into Power BI for analysis.

You must enter the correct URL and authentication to connect successfully.

Use the Navigator to select and load only the data you need for better performance.

Practice

1. What is the main advantage of using an OData connection in Power BI compared to a generic REST API?

easy

A. It cannot refresh data automatically.

B. It requires manual coding for every data request.

C. It only works with Excel files.

D. It provides a standardized query language and easier data filtering.

Solution

1. Step 1: Understand OData protocol features

   OData supports standardized query options like filtering, sorting, and paging directly in the URL.

2. Step 2: Compare with REST API flexibility

   REST APIs are flexible but often require custom query parameters and manual handling of data requests.

3. Final Answer:

   It provides a standardized query language and easier data filtering. -> Option D

4. Quick Check:

   OData = Standardized queries [OK]

Hint: OData uses standard queries; REST APIs need custom calls [OK]

Common Mistakes:

• Thinking OData only works with Excel
• Assuming REST APIs auto-refresh data
• Believing OData requires manual coding

2. Which Power Query M function is used to connect to a REST API endpoint in Power BI?

easy

A. OData.Feed()

B. Web.Contents()

C. Csv.Document()

D. Excel.Workbook()

Solution

1. Step 1: Identify function for REST API calls

   The Web.Contents() function fetches data from any web URL, including REST APIs.

2. Step 2: Differentiate from OData function

   OData.Feed() is specific for OData sources, not generic REST APIs.

3. Final Answer:

   Web.Contents() -> Option B

4. Quick Check:

   REST API = Web.Contents() [OK]

Hint: Use Web.Contents() for REST API URLs [OK]

Common Mistakes:

• Using OData.Feed() for REST APIs
• Confusing CSV import with API calls
• Trying Excel.Workbook() for web data

3. Given this Power Query M code snippet connecting to an OData feed:

let
  Source = OData.Feed("https://services.odata.org/V4/Northwind/Northwind.svc/Products"),
  Filtered = Table.SelectRows(Source, each [CategoryID] = 1)
in
  Filtered

What will be the result?

medium

A. An empty table because CategoryID is not a valid column.

B. An error because OData.Feed cannot filter data.

C. A table of products where CategoryID equals 1.

D. A list of all categories instead of products.

Solution

1. Step 1: Understand OData.Feed output

   The OData.Feed() function returns a table of products from the OData service.

2. Step 2: Analyze filtering step

   Table.SelectRows() filters rows where CategoryID equals 1, which is valid for this dataset.

3. Final Answer:

   A table of products where CategoryID equals 1. -> Option C

4. Quick Check:

   Filter by CategoryID=1 = filtered product table [OK]

Hint: OData.Feed returns tables; filter with Table.SelectRows() [OK]

Common Mistakes:

• Assuming OData.Feed cannot filter
• Confusing products with categories
• Thinking CategoryID column is missing

4. You wrote this Power Query M code to get data from a REST API:

let
  Source = Web.Contents("https://api.example.com/data"),
  Json = Json.Document(Source),
  Table = Table.FromList(Json, Splitter.SplitByNothing())
in
  Table

But you get an error saying "Expression.Error: We cannot convert the value to a table." What is the likely cause?

medium

A. The JSON returned is an object, not a list, so Table.FromList fails.

B. Web.Contents() cannot fetch JSON data.

C. Json.Document() only works with XML data.

D. Splitter.SplitByNothing() is not a valid function.

Solution

1. Step 1: Check JSON structure expected

   Table.FromList() expects a list, but if JSON is an object (record), it cannot convert directly.

2. Step 2: Understand error cause

   The error means the JSON is not a list but an object, so conversion fails.

3. Final Answer:

   The JSON returned is an object, not a list, so Table.FromList fails. -> Option A

4. Quick Check:

   JSON object ≠ list causes conversion error [OK]

Hint: Check if JSON is list or object before Table.FromList() [OK]

Common Mistakes:

• Thinking Web.Contents() can't get JSON
• Assuming Json.Document() only parses XML
• Believing Splitter.SplitByNothing() is invalid

5. You want to combine data from an OData feed and a REST API in Power BI. The OData feed provides product details, and the REST API returns live stock levels as JSON. Which approach is best to create a unified table for reporting?

hard

A. Use OData.Feed() to get products, Web.Contents() + Json.Document() for stock, then merge tables in Power Query.

B. Use only OData.Feed() for both sources since it supports all APIs.

C. Import REST API JSON as CSV and append to OData feed table directly.

D. Load both sources separately and create relationships in the data model without merging.

Solution

1. Step 1: Connect to each data source appropriately

   Use OData.Feed() for the OData product details and Web.Contents() with Json.Document() to parse REST API stock JSON.

2. Step 2: Combine data in Power Query

   Merge the two tables on product ID to create a unified table before loading into the report.

3. Final Answer:

   Use OData.Feed() to get products, Web.Contents() + Json.Document() for stock, then merge tables in Power Query. -> Option A

4. Quick Check:

   Combine sources with correct connectors and merge [OK]

Hint: Use correct connectors per source, then merge in Power Query [OK]

Common Mistakes:

• Trying to use OData.Feed() for REST API JSON
• Appending JSON as CSV without parsing
• Not merging data, just relating tables