web.contents with specified headers works in PBI desktop but fails in PBI Web

[bsjut]

Hi, 

we want to import Appannie (www.appannie.com) app download data into a PowerBI data model. Their API is fairly simple; I can connect in PowerBI desktop through the following blank query and get a proper JSON back. 

= Web.Contents(
"https://api.appannie.com/v1.2/accounts/[ACCOUNTID]/products/[PRODUCTID]/sales?break_down=date&start_...
[
Headers=[#"Authorization" = "Bearer [APIKEY]]
])

(please note I've anonymized API key, accontID and productID. 

Their authentication is done through a API key bearer approach (https://support.appannie.com/hc/en-us/categories/200261564-Analytics-API-v1-2-)  My hope was that we could also use this in the PowerBI service, but if we publish the PBIX file, I get a failed refresh with a 400 error. 

Are custom headers not supported in the PowerBI service?

Is there any known workaround?

[jackwaayer]

I have tried everything to authenticate my connection to a REST API from the Power BI service and had no luck. I am admitting defeat and assuming it is not possible.

I can also authenticate in PowerBI Desktop with many different methods. However, none of these methods work on the PowerBI service.

Has anyone found a work around for this?

[nsc]

Hi

Know this is a bit old, but the trick for me to get it working was two fold (both from Chris Webb).

1. Make use of relativepath and query as Chris explained here https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

2. Make sure your initial URL in the first part of the web.content call is valid, e.g. that you can access it without getting an error (if you cannot I think you are out of luck at this stage). See https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/ for details.

So what I did what rewrite the web.contents using the guidelines from 1. and then made sure that all my parameters where listed in either the relativepath part or the query part.

Note I had to authenticate using an apikey in the Header, so haven't tried using the ApiKeyName option.

Hope this helps.

regards Niels

[patter001]

Need help with this also...works fine on the first download, but refresh fails due to authorization

[nakia]

Hi All,

Having the same issue so wondering if anyone has found a work around for this?

Disapointing such a simple task is so complex...

[TrevMAU]

This is still an issue unfortunately.  Also related is this bugfix request, feel free to upvote (https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19202755-bypass-test-connection-i...)

So I've had to pull out the swiss army knife for fixing all things Power BI (R script):

require("httr")
require("jsonlite")
postQuery <- POST("https://rest.netsuite.com/app/site/hosting/restlet.nl", query = list(script=1, deploy=1), add_headers("Authorization"="NLAuth <auth>"), encode="json", body=list("searchID"="1", "min"="1", "max"="10"))
datatable <- fromJSON(content(postQuery, "text"), flatten=TRUE)

This was in order to pull data from a netsuite SuiteAdapter REST endpoint, but you should be able to switch out the pieces for whatever you are accessing (url, query, headers & body).  You could also use the httr GET command if you don't need POST.

PowerBI service will offload the processing to your local R instance via the Personal Gateway.  Great for pretty much anything PowerBI can't do yet.

[kobibarac]

Suffing from the same issue...
Any updates on this?

[sasmpu00]

Hello,

I am trying to get JSON from VSTS REST API. But I am not getting jason while using it power BI. It, returing web page content which is basically log in page. The below code, I have used. Could you please help me what I am missing.. 

Try 1:
let
authKey = "{""Username"":""XX"",""Password"":""XXX""}",
url = "https://davidscotttest.visualstudio.com/DefaultCollection/_apis/wit/workItems/12",
// Uses the authentication/token method to obtain a token
GetJson = Json.Document(Web.Contents(url,
[
Headers = [#"Content-Type"="application/json"],
Content = Text.ToBinary(authKey),
RelativePath="/Authentication/AuthorizeUser"
]
))
in
GetJson

try 2:

let
result = Web.Contents("https://davidscotttest.visualstudio.com/DefaultCollection/_apis/wit/workItems/12",[Headers=[#"Author... zc4cqxzc2k522zfzpueslrn3e3dvaxgxntpcnujnboksj7yico3a"]])
in
result

try 3:

let
result = Json.Document(Web.Contents("https://davidscotttest.visualstudio.com/DefaultCollection/_apis/wit/workItems/12", [Headers=[#"Authorization"="Token zc4cqxzc2k522zfzpueslrn3e3dvaxgxntpcnujnboksj7yico3a"]]))

in
result

[Anonymous]

Suggest you raise another thread as this is not really on topic.   That being said, you may wish look at the VSTS data source provided in Power BI rather than using the Web source.

[Anonymous]

I also have this issue when trying to publish to server.

If I try to use the APiKeyName option then Desktop complains that only Anonymous access is allowed for PUT requests so I am forced to hardcode the token into the header.   Despite this insistance on Anonymous access, Server insists that I provide credentials for the website but if I try to use the Anonymous option it fails.

And this is trying to connect to Microsoft's own Cognitive Services APIs.

[rdeken]

I'm having the same problem with a different RESTful web service.  Works in PBI Desktop but can't specify anonymous credentials on PowerBI.com.

[bsjut]

The new September update for PowerBI desktop seems to offer the ability to write custom headers. If this is also supported by PowerBI service, then this should work...

[Anonymous]

Hi, I am having the same problem. Using the Advanced feature of "From Web" does not seem to do its job correctly.

Adding a API source:

When pressing "Ok" and trying to connect I get prompted to provide login details (And I try to pick "Anonymous" since no usr/pw is required).

Looking at what is going on behind the scenes with Fiddler, it does not seem like any header is provided at all:

Trying the same thing. But instead of using the Guide for adding header in the "From Web" dialog, and instead using M language to add the header. Things get much better. (Token and API url are removed in the example)

let
    Source = Json.Document(Web.Contents("https://<<API-URL>>/21/api/0.1/", [Headers=[#"Authorization"="Token <<TOKEN>>"]])),
    toTable = Table.FromRecords({Source}),
....
....

And Fiddler is providing me with the information that an Authorization header is provided and a Status 200 is returned.

When I try to upload the report to powerbi.com (the one that is working) I get refresh error (when Im at powerbi.com) saying that the credentials are wrong, trying to atuthenticate anonymously does not work.

So,

1. It's not working with using the Header-functionality in the "From Web"-feature in PBI Desktop

2. It is working when passing the header directly within M-language.

3. Publishing the working PBI-file to powerbi.com seems to break the functionality that were previously working in the Desktop application.

Any thoughts?

[cwebb]

Have you tried using the ApiKeyName option with Web.Contents() as described here:

https://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/

? 

Chris

[Anonymous]

Seems like my post disappeared (or I am about to double post, apologizes if that the case)

Hi!

Thanks for your reply Chris.

I tried your solution but that does not really seem to do it?

When using my solution by storing the API-key directly in the code (not very good, i know)

let
    Source = Json.Document(Web.Contents("<<APIURL>>", [Headers=[#"Authorization"="<<APITOKEN>>"]])),
...
...

I get this in Fiddler (and its working in Desktop but not when published to powerbi.com though as stated earlier)

Trying the solution in your blog post does not seem to actually put in the header? More like it is putting the authorization directly in the GET URL

let
Source = Json.Document(Web.Contents("<<APIURL>>", [ApiKeyName="Authorization"])),
...
...

[cwebb]

That's strange. It's been a while since I last tested this, but when I wrote that post I'm pretty sure it was passing it through as a header. Did you get prompted to enter your key in the popup dialog as shown in my post when you first tried this?

Chris

[Anonymous]

Hi, yes I got prompted to enter credentials. Which I did, but as stated earlier, my input seems to go directly to the URL instead of being passed in the header. 

[cwebb]

I've just tested this again and my original example still works with the ApiKeyName option, so I'm not sure what the problem is in your case. In any case I'm not sure this will solve your original problem... Let me see if I can get someone from Microsoft to comment.

Chris

[cwebb]

I just did a few more tests, and it looks like using the ApiKeyName option isn't supported in the Power BI service anyway. However I was able to publish a report that uses the Headers option to pass an API key and that refreshes ok.

One thing though: after you published to the Power BI service, did you go into the settings for the data source in the browser and configure the data source credentials again? You can find this by clicking on the ellipses (...) next to the datset in the browser, choosing Schedule Refresh and opening the Data Source credentials section. You'll have to set the credentials to Anonymous again.

Chris

[Anonymous]

Hi Chris, thanks for your effort!

Yes I did try to set the credentials, but its failing... I dont know if the service for some security reason blanks M-code containing Header-information when uploading to PBI service? It's really odd..

[cwebb]

It can't be blanking out the header information because, as I said, I've just tested it and it works for me. Strange...

Can you post more of the M code for your query?