If you want to get data from an API into Power BI, it is often the case that you need to dynamically define the API call within the query itself. This can be due to various reasons, such as handling pagination, filtering data based on user inputs, or adjusting parameters based on previous API responses. This leads to a so-called dynamic data source. The problem with that is that dynamic data sources will often lead to an error during the scheduled refresh in Power BI Service.

Some Examples of when API calls need to be dynamically defined

1. Pagination: When an API returns data in pages, you need to adjust the offset parameter to retrieve subsequent pages.
2. Dependent Parameters: Sometimes, the parameters for an API call depend on the results of a previous call, requiring dynamic adjustments.

The Solution: Using the RelativePath and Query Option

The RelativePath and Query option in the Web.Contents() function offers a robust solution to this problem. By using RelativePath and Query, you can construct your API calls in a way that Power BI Service can handle during scheduled refreshes.

Here’s how you can implement it:

• Base URL: Define the base URL of your API. The Base URL is the static part of the API call that remains the same.
• Relative Path and Query: Use the RelativePath and Query option to specify the dynamic part of your API call.

Example

Imagine we want to query data about the sports on offer in Zurich and the region. We would use the following API for this:

https://www.zuerich.com/en/api/v2/data?id=97

The “id” part of the API controls which subcategory we want to retrieve. Subcategories include, for example, skiing & snowboarding, hiking or swimming. We want to be able to control this subcategory via a parameter. To achieve this we create a parameter called “subcategory” of type text. We can now use this parameter to control which subcategory we want to query.

Our Query would then look like this:

let
    BaseUrl = "https://www.zuerich.com/en/api", 
    Source = Json.Document(
        Web.Contents(
            BaseUrl, 
            [
                RelativePath="/v2/data", 
                Query=[id=subcategory]
            ]
        )
    )
in
    Source

In this example, the BaseUrl is the static part of the API endpoint, while the RelativePath and Query options handle the dynamic parts. This approach ensures that Power BI Service can refresh the data without encountering errors.

Conclusion

Using the RelativePath and Query option in the Web.Contents() function is a game-changer for handling dynamic data sources in Power BI. It allows you to build dynamic API calls that are compatible with Power BI Service’s scheduled refreshes, ensuring your data is always up-to-date without manual intervention.