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 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

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

Hello jimbob2285,

Thanks for the update and You've correctly implemented the RelativePath and Query structure, which is why it's now recognized as a valid static path and refreshes fine in Desktop. The issue you're seeing now in power bi service please consider raising a Microsoft support ticket for further investigation. You can explain all the steps you have taken to help them better understand the issue.

You can create a Microsoft support ticket with the help of the link below:
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket


Best Regards,
Ganesh singamshetty.

  • jimbob2285's avatar
    jimbob2285
    Advocate IV
    1 year ago

    Hi v-ssriganesh 

     

    Thanks for clarifying that I've got the RelativePath and Query code structured correctly

     

    I think I've sorted the credentials issue in the service

    • The solution I've found, which @florianh suggested in this thread: Failed to update data source credentials
    • Was to tick "Skip Test Connection" when editing the Data Source Credentials
    • I'm guessing that because of the way the URL is constructed in the M code, to bring back and combine all the paginated pages
    • That the Power BI Service only supplies the base URL when testing the connection
    • Which will obviously fail, as it doesn't contain the API Key
    • So, I presume skipping the test connection lest you set up the credentials
    • Which then work correctly when refreshing the data set, as the full url, with the API Key, is passed to the API

    Can you confirm that this is the accepted solution?

     

    Thanks for all your help with this, I simply wouldn't have got there without you

     

    Cheers

    Jim