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

[johnbasha33]

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.  🙂 😉

 

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. 🙂
            
            @mwegener