Efficient API Calls in Power BI: Using Open API Data fixing Power BI dataset error with Web.Contents()

When working with Power BI, efficiently retrieving data from APIs is crucial for performance and reliability, particularly when handling large datasets. This guide examines two different approaches to fetch data from the NHS UK API and explains why one method is more effective after publishing the dataset.

Scenario

You are tasked with fetching incident data from the NHS UK ServiceDesk API, specifically from the incident table. The API endpoint is https://servicedesk.NHS.UK.gov.com/api/now/table/incident.

I am sure some of you might have faced/come across with the error below when you are trying to connect to any API in Power Query.

I have got a solution for you. :) ;)

Approach 1: Ineffective After Publishing

Let's look at the first approach, which does not work reliably after publishing the dataset:

Approach 2: Effective After Publishing

Here's the improved approach that works effectively after publishing:

Key Differences and Advantages of the Second Approach

1. URL Construction

First Approach:

• Constructs the full URL by concatenating strings:

FullUrl = BaseUrl & Table & QueryParams & "&sysparm_offset=" & Text.From(Offset)

• This manual construction is prone to errors, especially with encoding and managing dynamic query parameters after publishing.

Second Approach:

• Uses a dynamic and flexible method to construct the URL with options:

Options = [

Headers=[Accept="application/json"],

Query = [

sysparm_fields = Text.From(Fields),

sysparm_query = Query,

sysparm_limit = Text.From(Limit),

sysparm_offset = Text.From(Offset),

sysparm_display_value = "true",

sysparm_exclude_reference_link = "true"

]

],

Source = Json.Document(Web.Contents(BaseUrl, Options)),

Data = try Source[result] otherwise null

in

Data,

• This method ensures query parameters are correctly encoded and managed, enhancing compatibility and reliability post-publication.

2. Query Parameter Handling

First Approach:

• Manually concatenates query parameters into a single string.
• More prone to errors and issues related to URL encoding, especially when interacting with different environments after publishing.

Second Approach:

• Uses the Query record within the Web.Contents function to manage query parameters.
• This abstraction reduces the complexity of encoding and managing query parameters, ensuring better performance and reliability.

Conclusion

The second approach demonstrates a more reliable method for fetching data from APIs in Power BI, especially after publishing the dataset. By dynamically handling query parameters and constructing URLs within the Web.Contents function, you ensure compatibility and reduce the likelihood of errors. When working with Power BI and APIs, always use a method that dynamically manages query parameters for improved performance and reliability.

Let me know if you want the sample code or any questions on this. :)