Data source accessing a web URL which requires Authentication headers

[Anonymous]

I have a PowerBI Desktop query/report which is working, but when I publish this into PowerBI Service, I have problems. The service cannot properly refresh the data source due to invalid credentials.

The system is retrieving data from the Harvest web API: https://help.getharvest.com/api-v2/

This API requires extra headers to be passed; the Authorization and Harvest-Account-Id headers, as described here:

https://help.getharvest.com/api-v2/authentication-api/authentication/authentication/

I have this working in PowerBI Desktop. This is the relevant part of the query, where the header values are passed in as parameters:

Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/time_entries?from=" & DateThreshold,
[Headers=[Authorization="Bearer " & HarvestAccessToken, #"Harvest-Account-ID"=HarvestAccountId, #"User-Agent"="PowerBI_DBRetrieval (" & HarvestUserEmail & ")"]])),

This datasource is set to use 'anonymous' credentials, because none of the other types of credentials seem relevant. And I have this successfully working, and refreshing, within PowerBI Desktop.

When I publish this to Power BI Service, it can no longer refresh the data. The refresh mechanism looks at the URL and tries it, to verify the credentials, but it doesn't add the extra headers. The API is returning 404 if the extra headers are not supplied, and therefore PowerBI Service tells me that it can't validate these credentials:

Failed to update data source credentials: Web.Contents failed to get contents from
'https://api.harvestapp.com/v2/time_entries?from=2017-01-01' (404): Not Found

Is there any way I can get the refresh mechanism to use the full headers?

It does seem to do some of the URL processing? Has anyone else managed to work around a similar problem?

[v-yuezhe-msft]

@Anonymous,

It doesn't support to refresh the data source in Power BI Service when passing parameter in URL as you mentioned. Please follow the guide in the blog below to specify Query option in the URL to workaround this issue,

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

Regards,
Lydia

[Anonymous]

Thank you.

I have now tried this with a parameterised query, but I get the same problem with the credentials. The credentials check uses the 'bare' URL, and doesn't pass the authentication headers, so the credentials check fails.

This is the URL I'm using now:

Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/time_entries",
        [
         Query=[from=DateThreshold],
         Headers=[Authorization="Bearer " & HarvestAccessToken, #"Harvest-Account-ID"=HarvestAccountId, #"User-Agent"="PowerBI_DBRetrieval (" & HarvestUserEmail & ")"]]
        )),

but when I try to refresh I get:

Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.

And, then the credentials gives this error:

Failed to update data source credentials: Web.Contents failed to get contents from 'https://api.harvestapp.com/v2/time_entries' (404): Not FoundHide details
Activity ID:    89558fd1-d30d-304f-c391-e67484bb4002
Request ID:    bf3b4ee0-9890-55f1-5389-37b07ff45fef
Status code:    400
Time:    Fri Jan 12 2018 10:17:20 GMT+0000 (GMT Standard Time)
Version:    13.0.3671.248
Cluster URI:    https://wabi-uk-south-redirect.analysis.windows.net

[v-yuezhe-msft]

@Anonymous,

Provide static values for these parameters(Authorization, User-Agent,etc) in your Headers, and combine RelativePath and Query option in your code. For more details, please review abedkhooli's reply in the following similar thread.

https://community.powerbi.com/t5/Service/Refreshing-a-Restful-Data-Source-with-API-Key/td-p/131298

Regards,
Lydia

[Anonymous · Accepted solution]

Thank you. I have now managed to spend some more time on this.

I can confirm that the suggested work-around to use the RelativePath parameter solves the problem.

I do not need to use static values for my Authorization parameters, which is good, because I don't want to have to duplicate these across many queries. This is the query which now works for me.

    Source = Json.Document(Web.Contents("https://api.harvestapp.com/",
        [
         RelativePath="v2/time_entries",
         Query=[from=DateThreshold],
         Headers=[Authorization="Bearer " & HarvestAccessToken, #"Harvest-Account-ID"=HarvestAccountId, #"User-Agent"="PowerBI_DBRetrieval (" & HarvestUserEmail & ")"]]
        )),

The important point, for this API, is that the root URL is not authenticated, so this passes the credentials check. When the real query is executed, the RelativePath, Query, and Headers are all correctly processed.