Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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
Source
The Content of "grant_type=client_credentials" seems to be required for PowerBI when handling the authentication yourself in the query.
Hi @grant_ar ,
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
output
The 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
Source
The Content of "grant_type=client_credentials" seems to be required for PowerBI when handling the authentication yourself in the query.
Hello Grant,
I am also working on similar type of requirement, where we are deploying our Power BI report as Power BI Template App into Microsoft AppSource. Our backend datasource is an API. Here, I need to get the bearer token of the user who signed into Power BI Service, and then pass that token in the Web Source as a Header.
Could you please help me how to get the bearer token from the User's Power BI sign in? Also please let me know if there is any expiry to the token in this scenario and if so, how to handle it?
Thanks
Lakshmi Koduri
I'm afraid I don't know how to dynamically get the PowerBI bearer token from the MSDN login process. In my case I was generating this manually and then adding it as a parameter in my template.
Would suggest asking the team at PowerBI directly to see if there's a recommended way of doing this.
Cheers
Hi Grant,
Thanks for the response. I need a quick help on your above response. Assuming that we own the application and want to manually generate bearer tokens, do we have to generate them from Power Query through any Power Query functions or through any other method which is outside Power BI completely and then use them as parameter inside our Web contents headers?
If through Power Query- do you have any sample code to generate the bearer token?
If outside Power BI - could you please provide what is the process to generate?
THanks
Lakshmi Koduri
Hi Lakshmi,
Sure here's how my example works I use a powerQuery function like this which takes any input (but does nothing with it) to authenticate and call the authentication URL shown and returns the bearer token
(_) as text =>
let
accesstoken = "token " & #"API",
url=Text.Combine({#"URL", "/api/tokens/authenticate"}),
response = Web.Contents(url,
[
Headers = [#"Authorization" = accesstoken],
Content = Text.ToBinary("grant_type=client_credentials")
]
),
Source = Json.Document(response),
authentication_data = Source,
#"Converted to Table" = Record.ToTable(authentication_data),
token = Text.Combine({"Bearer ", #"Converted to Table"{0}[Value]})
in
token
I then call this method like so later on in different queries:
token = #"Authenticate"(0),
Full_URL = Text.Combine({#"URL", URL_part}),
json = Json.Document(Web.Contents(Full_URL, [Headers = [#"Authorization"=token]])),
You could just as easily save the bearer token and just re-use it but some reason this worked out better for my use case, but I cannot remember why?
The #"API" above is manually generated within the application and is locked to my user account in the web app I'm querying.
Hope that helps.
Grant
Hi Grant,
Thanks for the reference query. It helped me for sure. But, I am using the following query to get the bearer token dynamically and passing that token to an API call in its headers. Its is working fine in Power BI Desktop but when the same report is published into Power BI Service, there the data source configuration is giving me a 400 error saying "Failed to update data source credentials. The credentials provided for the web source are invalid".
1. So to implement dynamic bearer token creation, user has to register a 3rd party application in their AAD and get the App Id which is the client id.
2. Then enable Power BI API read all permissions in the registered app. This is because I am trying to get the list of workspaces in Power BI through Power BI Rest API.
3. Now the power query goes like this:
let
// 1. Authentication set up
// a. Get the bearer token with a Http POST request
auth_key = "grant_type=Password&resource=https://analysis.windows.net/powerbi/api&client_id=blablablac&username=xyz@***.com&password=blablabl...",
Data=Web.Contents("https://login.microsoftonline.com/common/oauth2/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(auth_key)]),
Jsonfile= Json.Document(Data),
access_token= Jsonfile[access_token],
// b. Get the token from 1 and pass it in Headers of Http GET request
Source = Json.Document(Web.Contents("https://api.powerbi.com", [RelativePath = "/v1.0/myorg/groups", Headers=[Authorization="Bearer "&access_token]])),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"id", "name"})
in
#"Expanded Column2"
This query is working fine in Power BI Desktop, but not in Power BI Service. Don't know the reason for this error. Could you please take a look at it once?
Thanks
Lakshmi Koduri
Hi Lakshmi,
I'm afraid this is beyond my knowledge. I've barely used PowerBI in the cloud and my own queries work fine there. I would suspect the problem is from the app connection in PowerBI to allow you to get the users app and client ids and then update this powerBI but then I've never tried to do that myself so could be wrong but feels like the only part which would differ between desktop and Cloud environments.
Sorry I wasn't more help.
Grant
Hi @grant_ar ,
Does there miss a space between Authorization="token" and accesstoken? We think it should be
Authorization="token " & accesstoken
What 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?