Forum Discussion
How to pull data from Web API into PowerBI using Authorization header
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.
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
SourceThe 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
Hi Anonymous
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