REST API failing after passing token

[Anonymous]

Hi everyone,

I am trying to pull data from Bright Idea REST API. My call is working in PostMan, but in Power BI (Query Editor), the actual step needs authentication and, when I select Anonymous, it errors out saying "DataFormat.Error: We found extra characters at the end of JSON input."

I would expect the following code to work seamlessly, as I am providing an access bearer token in Authorizaiton header, however, its not.

Fiddler Raw Request Capture for PostMan

GET https://xxx.brightidea.com/api3/idea HTTP/1.1
Authorization: Bearer 79cd41565f54ce0fed499efac09eb68c9540fe7f
User-Agent: PostmanRuntime/7.13.0
Accept: */*
Cache-Control: no-cache
Postman-Token: 438d7615-f7b7-40e9-af15-615eee437ca1
Host: xxx.brightidea.com
cookie: csrf=RowsMlstCHfdPMWtffuoveA_0ejScx4sFdk4xvqbJgeVxpllcoNBPeQ
accept-encoding: gzip, deflate
Connection: close

Power Query code that shows Credentials button

= Json.Document(Web.Contents("https://xxx.brightidea.com/api3/idea", [Headers=[#"Authorization"="Bearer 79cd41565f54ce0fed499efac09eb68c9540fe7f", #"Content-Type"="application/json", expires_in="600"]]))

I'd appreciate your help.

Thanks
kaz

[d_gosbell]

I've used this technique of injecting the bearer token and it worked fine. Try removing the outer call to Json.Document so that you can just see the return from Web.Contents and examine the end of the response to see if it has extra characters or something. Or you could try using something like Fiddler to examine the web traffic between Power BI and the brightidea server.

[Anonymous]

Thanks @d_gosbell. I have cracked the original issue by removing the "Content Type" header, and it works. 

I have ran into another one - all this time, I was hard coding the token that I got from Post Man. For some odd reason, the access token I obtain as part of the original request in Power BI doesn't work. I have tried to use it in Post Man, that is, generate token in Power BI, copy and paste it in Post Man, it gives me an invalid grant message. 

If I generate the token in Post Man and use it in Power BI, it works. 

Any idea what could be going wrong?

[Anonymous · Accepted solution]

Cracked it, it was something do with privacy levels. 

So in summary - at the time of doing the actual GET request to the API, I had to take off the "Content Type = application/json" header. Second, I set the data privacy levels to None to the datas source, and it got to work.