Unable to load JSON data from Azure dev RESTful Service

[atulsureka]

Hi

I am new to PowerBI, I have VSTS project. I would like to use Azure dev Restful APIs for pull requests. The URL looks like this https://dev.azure.com/<org-name>/<project>/_apis/git/repositories/<repo id>/pullRequests/<pr-id>/thr...

If i open the URL I get a JSON content as output. I would like to open the same in PowerBI Desktop. I tried using the option New DataSource --> Web --> Gave the same URL --> I get the following error.

------------

DataFormat.Error: We found extra characters at the end of JSON input.
Details:
Value=
Position=4

------------

JSON format is correct if I open the same URL in browser. Can any one please tell what could be the reason.

Regards,

Atul Sureka

[arsaveli]

The reason you get this error is that you are receiving login screen html.

You can use VSTS.AccountContents function to hit REST APIs which will trigger correct authentication.

Or you can use Web.Contents but please generate and use PAT to log in.

[atulsureka]

I tried using PAT with http call, following is content of my query

= Json.Document(Web.Contents("https://dev.azure.com/test/projectname/_apis/git/pullrequests?searchCriteria.repositoryId={92dbae00-...", [Headers=[Authorization="Basic OmgzrewrerwwerewrewrGVxcm9vamkzewrewrwewrewrdjIyd3d4Y2Zscmc2eTdjNTM0eWE=", #"Content-Type"="application/json"]]))

I get error 

Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Referer

I could not find any option to connect anonymously. can someone please tell me how to set authentication option in power BI with web request.

Atul

[atulsureka · Accepted solution]

Just in case anyone else still facing this issue, issue is resolved for me.

I should be using PAT by setting in header. Also Data Source Settings needs to be modified to set to send request anonymously. This setting is present under "Edit Queries" --> "Data Source Settings" --> "Edit Permission".

[atulsureka]

Yes, it seems I am getting authentication issues, it is actually returning HTML rather JSON. Can you please point me how to PAT with JSON VSTS RESTful APIs request, Any sample or link would be helpful.

Atul

[v-juanli-msft]

Hi @atulsureka 

You could open edit queries->Advanced editor, write code direct here to connect to json data.

in this code, please use the correct connection and use "comma" or some transformation to transform json data to normal tabular data in power bi.

Here are some references from another similar thread.

https://community.powerbi.com/t5/Desktop/We-found-extra-characters-at-the-end-of-JSON-input/td-p/206...

In addition, Reference about connecting to VSTS with Power BI:

https://community.powerbi.com/t5/Service/Power-bi-online-integration-with-Visual-studio-team-service...

https://devblogs.microsoft.com/devops/power-bi-and-vsts-integration-made-easy/

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.