cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Data source accessing a web URL which requires Authentication headers

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

Re: Data source accessing a web URL which requires Authentication headers

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.

 

View solution in original post

4 REPLIES 4
Highlighted
Microsoft
Microsoft

Re: Data source accessing a web URL which requires Authentication headers

@conoroneill,

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


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: Data source accessing a web URL which requires Authentication headers

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

 

Highlighted
Microsoft
Microsoft

Re: Data source accessing a web URL which requires Authentication headers

@conoroneill,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: Data source accessing a web URL which requires Authentication headers

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.

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors