Forum Discussion

jimbob2285's avatar
jimbob2285
Advocate IV
1 year ago
Solved

Can't refresh dynamic API URL in Power BI service

Hi   I have a paginated API data source in a Power BI report, that used the following M code to iterate through and combine multiple pages into a single table, but I then can't refresh the report o...
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    Hello jimbob2285,
    Thanks for getting back and for providing those additional details.

    You're absolutely on the right track using RelativePath with Web.Contents is the correct solution to make your API call refreshable in the Power BI Service. You're very close, and the issue now seems to be related to how the parameters and authentication are being passed in the M code.

    Key things to check:

    • Keep your BaseUrl simple and static. It should not include parameters or dynamic segments.
    • Use RelativePath only for the fixed portion of the endpoint path like /v1/organizations.
    • Pass start, limit, and api_token using the Query option as a record. This is critical Power BI expects query parameters to be passed in this structured format to allow refresh in the Service.
    • Avoid string concatenation inside RelativePath or Query. These should be clearly separated.
    • If your API uses an API key in the query string, include it in the Query record as well. If it uses headers, you’ll need to configure it using the Headers option inside Web.Contents.

    Here are two official docs that explain this pattern clearly:

  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    Hello jimbob2285,
    Thanks for sharing more details, you're really close. The issue now is with how the Query parameter is being passed.

    Power BI expects Query to be in a structured format using key-value pairs, not as a single concatenated string like "api_token=XXXX". This is a common roadblock. Instead of building one string, you should treat each query parameter as a separate item in a list (or "record") so Power BI can safely handle them and construct the URL properly for both Desktop and Service refresh.

jimbob2285's avatar
jimbob2285
Advocate IV
1 year ago

Hi

 

thanks BA_Pete  and v-ssriganesh  for your responses, but I just can't get the syntax right, I'm new to M code and while RelativePath is clearly the answer, to my problem, I can't get it to work - It's failing to authenticate, which tells me I'm nearly there, but not quite

 

Can anyone help me get this right please? I can't find a decent guide to RelativePath anywhere online and I just don't understand the syntax

 

// Dynamic Path - Won;t allow refresh from Power BI Service
//                Url = BaseUrl & "start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "&api_token=" & ApiToken,
//                Response = Json.Document(Web.Contents(Url)),

// Attempt at Static Path - To allow refresh from Power BI Service 
                Url = "start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "&api_token=" & ApiToken,
                Response = Json.Document(
                    Web.Contents(
                        BaseUrl, [
//                            RelativePath = "/v1/organizations?start=" & Url
//                            RelativePath = "/v1/organizations?start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "api_token=" & ApiToken
//                            RelativePath = "/v1/organizations?",
                            RelativePath = "/v1/organizations?start=" & Text.From(Start) & "&limit=" & Text.From(Limit),
//                            Query = "start=" & Text.From(Start) & "&limit=" & Text.From(Limit),
                            ApiKeyName = "api_token=" & ApiToken
//                            Authorization = "api_token=" & ApiToken
                        ]
                    )
                ),

 

Cheers

Jim

v-ssriganesh's avatar
v-ssriganesh
Community Support
1 year ago

Hello jimbob2285,
Thanks for getting back and for providing those additional details.

You're absolutely on the right track using RelativePath with Web.Contents is the correct solution to make your API call refreshable in the Power BI Service. You're very close, and the issue now seems to be related to how the parameters and authentication are being passed in the M code.

Key things to check:

  • Keep your BaseUrl simple and static. It should not include parameters or dynamic segments.
  • Use RelativePath only for the fixed portion of the endpoint path like /v1/organizations.
  • Pass start, limit, and api_token using the Query option as a record. This is critical Power BI expects query parameters to be passed in this structured format to allow refresh in the Service.
  • Avoid string concatenation inside RelativePath or Query. These should be clearly separated.
  • If your API uses an API key in the query string, include it in the Query record as well. If it uses headers, you’ll need to configure it using the Headers option inside Web.Contents.

Here are two official docs that explain this pattern clearly:

  • jimbob2285's avatar
    jimbob2285
    Advocate IV
    1 year ago

    Hi v-ssriganesh 

     

    Thanks for this, I've already seen both those two docs and didn't find them very helpful.  Despite my best efforts, I can't seem to make this work, it's failing authentication, as if it's not paasing the API key correctly.

     

    So I've stripped it back to a more simple, none paginated query on the same endpoint:

    • So, there are no Start or Limit parameters to pass
    • Switching the Auto-generated M code for the RelativePath and Query approach (See below)
    • But I still can't get it to work
    • However, concatenting these three aspects together in the standard (non RelativePath and Query) method works fine

     

    I've tried to follow your guidance but I'm not sure what you mean by:

    • Power BI expects query parameters to be passed in this structured format, do you mean that i can pass several Query Parameters, but it didn;t seem to like any more than 1 query parameter
    • Avoid string concatenation inside RelativePath or Query. These should be clearly separated - i think I've done this, but it still doesn't like it 

     

    let
    BaseUrl = [Base URL],
    ApiToken = "api_token=" & [API Token],
//    Source = Json.Document(Web.Contents(BaseUrl & "/api/v1/organizations?" & ApiToken))
    Source =    Json.Document(
                    Web.Contents(
                        BaseUrl, [
                            RelativePath =  "/v1/organizations?",
                            Query = ApiToken
                        ]
                    )
                )
in
    Source

     

    I just can't work out what I'm doing wrong here - it looks like it should work, but it's not... What am i doing wrong?

     

    Cheers

    Jim

    • v-ssriganesh's avatar
      v-ssriganesh
      Community Support
      1 year ago

      Hello jimbob2285,
      Thanks for sharing more details, you're really close. The issue now is with how the Query parameter is being passed.

      Power BI expects Query to be in a structured format using key-value pairs, not as a single concatenated string like "api_token=XXXX". This is a common roadblock. Instead of building one string, you should treat each query parameter as a separate item in a list (or "record") so Power BI can safely handle them and construct the URL properly for both Desktop and Service refresh.

      • jimbob2285's avatar
        jimbob2285
        Advocate IV
        1 year ago

        Hi v-ssriganesh 

         

        Sorry, I got dragged into another task for the last couple of days

         

        I've now adapted the code as below, which refreshes fine in Power Query and Desktop, but not in the Service

         

        Response = Json.Document(
    Web.Contents(
        BaseUrl, [
            RelativePath =  "/v1/organizations?",
            Query = [
                start = Text.From(Start),
                limit = Text.From(Limit),
                api_token = ApiToken
            ]
        ]
    )
 )

         

         

        So it feels like the original problem is solved, as Power BI service no longer sees it as a dynamic URL.  But, I now don't understand why it will refresh in Desktop, but not in the Service.

         

        It's clearly got the right credentials and seems to be written correctly now (please verify) as it refreshes fine in Power Query and in Power BI Desktop, but in the Service it throws up the followign error:

         

        " Failed to update data source credentials: The credentials provided for the Web source are invalid"

         

        If we reduce this to a simplar query, as below, taking out the Start and limit parameters:

         

        let
    BaseUrl = "https://fulcrumgroupholdinglimited.pipedrive.com",
    ApiToken = [API Token],
//    Source = Json.Document(Web.Contents(BaseUrl & "/api/v1/organizations?api_token=" & ApiToken)),
    Source = Json.Document(
        Web.Contents(
            BaseUrl, [
                RelativePath =  "/api/v1/organizations?",
                Query = [
                        api_token = ApiToken
                ]
            ]
        )
    )
in
    Source

         

         

        When I use the concatenated (commented out) source it works fine in the service, but using the RelativePath & Query approach it doesn't.  What I can see, when I edit the credentials in the service, is that the URLs are different between:

        So, no wonder it's failing to authenticate, it's looks like it's missing the table and API Token details

         

        But then why does it refresh fine in Power BI Desktop... I'm obviously still doing something wrong?

         

        So, as ever, I'm a bit lost - Any ideas?

        Cheers

        Jim