OData and REST API connections in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding OData vs REST API in Power BI

Which statement correctly describes a key difference between OData and REST API connections in Power BI?

A OData connections only support XML format, whereas REST API supports JSON only.

B REST API automatically generates data models, but OData requires manual data shaping.

C OData provides a standardized queryable data feed, while REST API requires custom parsing of JSON responses.

D REST API connections in Power BI do not support authentication, but OData does.

💡 Hint

Think about how Power BI interacts with data formats and query standards.

OData is a standardized protocol that allows Power BI to query data feeds directly with filters and selections. REST APIs often return JSON that requires custom parsing and shaping.

❓ dax_lod_result

intermediate

DAX Measure for Counting API Calls by Date

Given a table 'ApiCalls' with columns 'CallDate' and 'Status', which DAX measure correctly counts the number of successful calls (Status = "Success") per day?

Power BI

Measure = CALCULATE(COUNTROWS(ApiCalls), FILTER(ApiCalls, ApiCalls[Status] = "Success"))

A Measure = COUNTROWS(FILTER(ApiCalls, ApiCalls[Status] = "Success"))

B Measure = CALCULATE(COUNTROWS(ApiCalls), FILTER(ApiCalls, ApiCalls[Status] = "Success"))

C Measure = COUNTROWS(ApiCalls[Status] = "Success")

D Measure = CALCULATE(COUNT(ApiCalls[Status]), ApiCalls[Status] = "Success")

💡 Hint

Remember that FILTER returns a table and CALCULATE modifies filter context.

Option B correctly uses CALCULATE with FILTER to count rows where Status is "Success". Option B is valid DAX and will work correctly. Option B is syntactically wrong. Option B misuses COUNT and filter syntax.

❓ visualization

advanced

Best Visualization for API Response Time Analysis

You have connected Power BI to a REST API that returns response times for each API call. Which visualization best helps identify slow response trends over time?

A Line chart showing average response time per day

B Pie chart showing percentage of calls per status

C Stacked bar chart showing count of calls by endpoint

D Table listing all API calls with response times

💡 Hint

Think about how to show trends over time clearly.

A line chart is ideal for showing trends in average response time over days, making it easy to spot slowdowns. Pie charts and stacked bars do not show trends over time. Tables show details but not trends visually.

🔧 Formula Fix

advanced

Debugging REST API Connection Failure

You set up a REST API connection in Power BI but get an authentication error. Which of the following is the most likely cause?

A The data returned is in JSON format instead of XML.

B The API endpoint URL is missing the query parameters.

C The Power BI desktop version is outdated.

D The API key was not included in the HTTP request headers.

💡 Hint

Authentication errors usually relate to missing or incorrect credentials.

Missing API key in headers causes authentication failure. Missing query parameters or data format issues cause different errors. Power BI version rarely causes authentication errors.

❓ data_modeling

expert

Modeling Data from Multiple OData Feeds

You have two OData feeds: 'Customers' and 'Orders'. Each feed is connected separately in Power BI. What is the best way to model these tables to analyze total sales per customer?

A Create a relationship between 'Customers[CustomerID]' and 'Orders[CustomerID]' and use a measure to sum order amounts.

B Append both tables into one combined table and sum sales directly.

C Use only the 'Orders' table since it contains customer IDs and sales data.

D Create a calculated column in 'Customers' that fetches order totals via API calls.

💡 Hint

Think about how relational data is modeled in Power BI.

Creating a relationship between the two tables on CustomerID allows you to analyze sales per customer efficiently. Appending tables mixes different entities. Using only Orders loses customer details. Calculated columns with API calls are inefficient and unsupported.

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