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?

[Anonymous]

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

I stripped the code down to the basics, still giving me the error :/. What you see above is the only thing going on in the file. The only thing I could think of is the fact that the information that is passed in the Autorization-tag is "Token <<API-TOKEN>>". It's not just plainly a API-key since it starts with the word "Token" and is followed by the actual key, I dont know if that is even possible to be an issue?

[cwebb]

Hmm, one last guess - can you try separating the call to Web.Contents() from Json.Document()? ie something like this:

let
    Source = Web.Contents("<<API-URL>>", [Headers=[#"Authorization"="Token <<API-TOKEN>>"]]),
    convertToJson = Json.Document(Source),
    toTable = Table.FromRecords({convertToJson})
in
    toTable

[Anonymous]

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"])),
...
...

[Anonymous]

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

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 a Authorization header is provided and a Status 200 is returned.

And ofcourse, when I try to upload the report to powerbi.com (the one that is working) I get refresh error saying that the credentials are wrong.

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?

[bsjut]

Ah, that's disappointing! 

We'll check in the next days, if we can reproduce the issue. 

Have you raised the PBI desktop issue with a frown? 

[Anonymous]

Hi, I am not sure how to send a frown since its not giving me and "error", just plainly saying that the request is unauthorized since the header is not passed.

[bsjut]

Hmm,

It's probably best to write to pbidesfb AT microsoft DOT com - that's the email address where frown tickets go to - to raise this issue appropriately.

[Anonymous]

Thanks, I've sent them an email with the details. Hoping for a solution since this is a go/no go for our customer. 

[Anonymous]

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

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/consultation/", [Headers=[#"Authorization"="Token <<TOKEN>>"]])),
    toTable = Table.FromRecords({Source}),
....
....

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

And ofcourse, when I try to upload the report to powerbi.com (the one that is working) I get refresh error saying that the credentials are wrong.

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?

[Anonymous]

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

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/consultation/", [Headers=[#"Authorization"="Token <<TOKEN>>"]])),
    toTable = Table.FromRecords({Source}),
....
....

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

And ofcourse, when I try to upload the report to powerbi.com (the one that is working) I get refresh error saying that the credentials are wrong.

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?

[bsjut]

[abaconsup]

I think I just hit the same problem.

I'm managing to connect to FreeAgents API (via api.sandbox.freeagent.com, see https://dev.freeagent.com/docs/oauth) using an access TOKEN (obtained via Google's OAuth2.0 playground) in the header request

= Json.Document(Web.Contents("https://api.sandbox.freeagent.com/v2/company",[Headers=[#"Authorization"="Bearer TOKEN"]]))

Using an Anonymous Data Source Setting.

This works fine from desktop, but when the data set is published and refreshed in Power BI service it fails.

I see this error when I refresh the dataset

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

Presumably the header is stripped from the dataset when published to Power BI?

[eddablin]

Hi please can you shed some light on how you did this?

I am using the following using the access code form the oauth 2.0 playgorund

let
Source = Web.Contents("https://api.freeagent.com/v2/invoices", [Headers=[Authorization="xxx"]]),
convertToJson = Json.Document(Source)
in
convertToJson

But I get the follwoing message:

DataSource.Error: Web.Contents failed to get contents from 'https://api.freeagent.com/v2/invoices' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.freeagent.com/v2/invoices
Url=https://api.freeagent.com/v2/invoices

[marteken]

Hi abaconsup,

Were you able to solve this?

Thanks

[abaconsup]

Hi marteken,

I'm afraid not, I just tried this again and got the same error.

I'm going to log a support call with MS to find out more, but in the meantime you can vote up the feature here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13195278-oauth

[Syam]

I also face the similar problem. 

I am generating token in Power query itself by calling authentication method and passing the same generated token for api call.

Token generation

Token = Web.Contents(url,
[
Headers = [#"Content-Type"="application/json"],
Content = Text.ToBinary(authKey),
RelativePath="/Authentication/AuthorizeUser"
]
)

Calling api for Tenants

Source= Web.Contents("https://aamm.api.rentmanager.com",
[Headers=[#"X-RM12Api-ApiToken"=token],RelativePath="/Tenants"])

Like wise multiple tables are created in Power Bi model. It worked perfectly in Power BI desktop. But in Power BI webservice it did not able to refresh. Shows the below error.

The credentials provided for the Web source are invalid. (Source at https://aamm.api.rentmanager.com/Tenants.)

I suppose this is happening because when refresh happens all tables simultaneously load, so each of them has different token which make Api call to fail.

So I have created function to generate token and pass to all api calls, so the same token is used.Again it worked in Power BI deaktop file. But it did not work in Power BI service. Got the same failure message.

I need urgent help to fix this. Your help is higly appreciated.