Forum Discussion
How to pull data from Web API into PowerBI using Authorization header
curl --location --request POST 'https://<URL>/api/tokens/authenticate' \
--header 'Content-Type: Application/json' \
--header 'Authorization: token <TOKEN>'
curl --location --request GET 'http://<URL>/api/current-user' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer <BEARER-TOKEN>'
Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Referer
let
accesstoken=#"API Token",
url=Text.Combine({#"URL","/api/tokens/authenticate"}),
Source = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json", Authorization="token" & accesstoken]])),
authentication_data = Source,
#"Converted to Table" = Record.ToTable(authentication_data)
in
#"Converted to Table"
All my attempts so far to figure this out have failed as all the existing posts of tutorials appear to referance examples which don't apply here (i.e. non-authenticated URLs or different auth methods). Is there something simple I'm doing wrong here or is this just something PowerBi does not support?
So I've found the solution and it was due to a PowerBI expecting and requiring a content in the API request. Not sure why this is required by PowerBI but this is the magic step I was missing. Full simplified example below:
let accesstoken = "token " & #"API", url=Text.Combine({#"URL", "/api/tokens/authenticate"}), Source = Json.Document( Web.Contents(url, [ Headers = [#"Authorization" = accesstoken], Content = Text.ToBinary("grant_type=client_credentials") ])) in SourceThe Content of "grant_type=client_credentials" seems to be required for PowerBI when handling the authentication yourself in the query.
12 Replies
Hi Anonymous ,
Does there miss a space between Authorization="token" and accesstoken? We think it should be
Authorization="token " & accesstokenWhat is the error shown in Power Query Editor if run the sample query which get the bearer token?
Best regards,have you read this post already? it seems very similar:
I assume you get the error in the Source step, correct? Does it work if you hardcode the url and the manually generated token?
Hi Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,The issue is not with the query itself but good spot on the typo. The problem is PowerBI doesn't seem to understand or accept that I'm trying to formulate my own query and instead insists on asking me to configure the connection source. See linked image:
let WebTimeout = #duration(0,0,0,100), accesstoken = #"BD API", url = Text.Combine({#"BD URL","/api/tokens/authenticate"}), WebResponse = Web.Contents(url, [Headers = [Authorization="token " & accesstoken, #"Content-Type"="application/json", Accept="application/json"], Timeout = WebTimeout]), output = formatfunction(WebResponse) //format the response in outputThe problem is none of the possible options work with my type of application. The options I get given are somewhat limited and I'm unable to customise these: Anonymous, Windows, Basic, Web API, Organizational account.
It feels like there should be setting to just let me manage my own queries without having to tell powerBI how to connect but I've not yer found that option and doubt it exists.
Thanks for your help
So I've found the solution and it was due to a PowerBI expecting and requiring a content in the API request. Not sure why this is required by PowerBI but this is the magic step I was missing. Full simplified example below:
let accesstoken = "token " & #"API", url=Text.Combine({#"URL", "/api/tokens/authenticate"}), Source = Json.Document( Web.Contents(url, [ Headers = [#"Authorization" = accesstoken], Content = Text.ToBinary("grant_type=client_credentials") ])) in SourceThe Content of "grant_type=client_credentials" seems to be required for PowerBI when handling the authentication yourself in the query.
I have a similar issue, on the desktop side, all works, I have a function to call the token one time, and then use that token in all the subsequent calls, in Dektop irt refreshes fine. When I publish that, the services asks me to Edit Credentials to the soruces, what credentials? all is within the model, refresh token, token, client id, all that is within the PBI model, how do I configre in the service so it refreshes?
