cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Commander
Kudo Commander

Re: Issue with getting data via API with bearer token

@jlorenzo @kailashkrishnan @v-shex-msft thanks for your input.

 

Just realised there had been further commentary on this so I thought I'd better close out with my resolution.

 

@jlorenzoI had the same issue. Managed to get the functionality to work from Desktop, but when you push to service, it has authorization issues.

 

My workaround that I settled on was to make Azure Functions for the API calls which I have scripted in C# then I call the Azure Function as an API.

Highlighted
New Member

Re: Issue with getting data via API with bearer token

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

Highlighted
Anonymous
Not applicable

Re: Issue with getting data via API with bearer token

Hi @aexley,

 

Hope you get solution for this issue,if you dont mind can share the code..im also facing the same issue

 

Thanks in advance.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors