cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
grant_ar
Regular Visitor

How to pull data from Web API into PowerBI using Authorization header

I'm trying to pull data from a web Server in PowerBI. The web server uses Oauth and requires you to first create a bearer token by posting to the Authentication endpoint as shown below:

 

curl --location --request POST 'https://<URL>/api/tokens/authenticate' \
--header 'Content-Type: Application/json' \
--header 'Authorization: token <TOKEN>'

 

 

This will return the bearer token which I can then use on subsequent requests to get the data I need into PowerBI. Below is an example request:

 

curl --location --request GET 'http://<URL>/api/current-user' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer <BEARER-TOKEN>'

 

 
I know how to construct these queries in PowerQuery however my issue is in the data source methods which PowerBI employees. No matter what I try I either get an error stating that the authorization header can only be used on anonymous data sources or I get an error asking me to specify how to connect which then bring up the web data source page.
 

 

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

 

All the options I've tried have failed thus far I've tried using the Anonymous data source but this fails as it won't authenticate to the base URL i.e. https://something.com/. I've also tried using the Web API option but this fails as it asks for a authentication token name but then doesn't provide any clues on how to provide this?
 
Below is the example query I'm trying to use to get the bearer token and this should work but PowerBi does'nt understand my data source and I'm unsure on how to configure this correctly.

 

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?

1 ACCEPTED 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.

View solution in original post

11 REPLIES 11
v-lid-msft
Community Support
Community Support

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 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:

https://ibb.co/pzQPpFw 

 

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.

View solution in original post

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 @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

 

 

v-lid-msft
Community Support
Community Support

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Stachu
Community Champion
Community Champion

have you read this post already? it seems very similar:

https://community.powerbi.com/t5/Desktop/Pull-data-from-RESTful-API-with-token-authentication/m-p/43...

 

I assume you get the error in the Source step, correct? Does it work if you hardcode the url and the manually generated token?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors