Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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 Microsof...
Need Help
krenting's avatar
krenting
New Member
8 years ago

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