Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 --l...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

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,

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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