Forum Discussion
Issue with getting data via API with bearer token
Hi,
I am using an API that requires a bearer token embedded in the header that has a 30 min lifespan. I have constructed a Power Query ("M") that will pass the username and password to a Microsoft OWIN oauth token endpoint and this returns the required token correctly.
let
url = #"Token URL",
GetJson = Web.Contents(url,
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("username=XXXXXXX&password=XXXXXXXX&grant_type=password")
]
),
FormatAsJson = Json.Document(GetJson),
// Gets token from the Json response
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,
GetJsonQuery = Web.Contents(#"Search URL",
[
Headers = [#"Authorization"=AccessTokenHeader]
]
),
FormatAsJsonQuery = Json.Document(GetJsonQuery)
in
#"FormatAsJsonQuery"
I then try to pass that token through to a Web.Contents function to process the API query with the authorisation token to pull the required data into Power BI.
On running this I get a prompt saying "Access to the resource is forbidden. Edit Credentials". So I try setting the credentials to anonymous but it keeps returning "Access is forbidden". This is probably due to the requirement of the "Authorization" key to pass the token. My assumption is Power BI then tries to intercept this and provide the authorisation prompt but this will not work for my requirement.
I know the token that is returned is working for authentication as I can use it externally from Power BI to run a GET query effectively, I just can't make this function inside Power BI.
Is there something I'm missing? Researching this proves fairly light on details and contradictory answers for this use case so if someone could clarify any experience it would be appreciated.
Thanks.
12 Replies
Hi Anonymous,
I think your formula based on below article, right?
Get Data from Twitter API with Power Query
Web.Contents method not support directly add username and password to content query.
Usually logic of request:
1. Call login API to get the access token.
2. Use access token call other operation api.
Sample:
WebResponse = Web.Contents(WebServiceURI, [Content = Text.ToBinary(WebServiceContent), Headers = [Authorization="Bearer " & AccessToken, #"Content-Type"="application/json", Accept="application/json"], Timeout = WebTimeout])In addition, API can allow "username", "password", "token" as the parameters to query string, but they must be defined in design.
For example:
RequestUri= BasUri?UserName:xxxxx&Passowrd:xxxxxx&Token:xxxxx
I'd recommend you take a look at the Official documentation and find out the correct way to call api.
Regards,
Xiaoxin Sheng
Thank you Xiaoxin Sheng ( Anonymous ),
The logic you have provided is what I am trying to perform as the token has a 30 min lifespan and I can't get users of the report to manually enter a token each time.
To call a token I have to send a POST request with the username, password and grant_type embedded as content. The API does not accept a URI with parameters to return a token.
This works to return my token:
GetJson = Web.Contents(url, [ Headers = [#"Accept"="application/json", #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"], Content = Text.ToBinary("username=uXXXXXX&password=pXXXXXX&grant_type=password") ] ), FormatAsJson = Json.Document(GetJson), // Gets token from the Json response AccessToken = FormatAsJson[access_token], AccessTokenHeader = "bearer " & AccessTokenThis will give me a variable (AccessTokenHeader) with the access token. If there is another way to do this please let me know.
Hi Anonymous,
Can you share some detail content of the api which you want to invoke?
>>The logic you have provided is what I am trying to perform as the token has a 30 min lifespan and I can't get users of the report to manually enter a token each time.
You can write a custom function to get token which your account, then invoke this method before operation other api.
Regards,
Xiaoxin sheng
The problem is that PowerBI.com first tries to load the json from the url given without the parameters. The return value will not be in the correct format because most API's will return an error. After failing this test it is impossible to refresh your data using your parameters.
let url = #"Token URL", <-- THIS URL NEEDS TO ALLWAYS RETURN THE SAME CORRECT DATA STRUCTURE EVEN WITHOUT THE HEADERS AND POST VALUES GetJson = Web.Contents(url, [ Headers = [#"Accept"="application/json", #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"], Content = Text.ToBinary("username=XXXXXXX&password=XXXXXXXX&grant_type=password") ] ), FormatAsJson = Json.Document(GetJson),
I created a workaround by returning a json document containing fake data in the correct structure when the API was called without proper authentication.When you do this and set authentication to anonymous at PowerBI.com the first test will pass. PowerBI.com will now know that your API returns data in the correct format and will execute your Power Query during refresh returning the correct data.
Regards,
Kees Renting
