Reply
Highlighted
Frequent Visitor
Posts: 2
Registered: ‎12-20-2018
Accepted Solution

Cannot connect to JSON file located on SharePoint

[ Edited ]

When attempting to connect Power BI to a JSON file on SharePoint, and used the Copy link URL of the JSON file.  Received the following:

 

Unable_to_connect.jpg

 

The JSON file points to an URL API.  There are no issues when connecting to the JSON file when it is located on the desktop, nor when connecting to the URL API directly.

 

I then used the following Power Query M script, which produces the web content in text form, but cannot convert back to JSON.

 

let
         Source = Text.FromBinary(Web.Contents("https:/sharepoint.com/:u:/s/site/file")),
         Custom1 = Text.Range(Source, 1, Text.Length(Source) - 2),
         Custom2 = Json.Document(Custom1)
in
         Custom2

 

The output displays all the web contents, but the Custom 2 displays the following:

 

custom2.jpg

 

Would anyone know why Power BI cannot connect to a JSON file on SharePoint:

 

1. Is this an issue getting an API token from SharePoint, and putting the credentials in the Query?

2. Is this an issue getting an API token from the URL API, and putting the credentials in the Query?

3. Could it be that the JSON file URL path does not contain the file type at the end, in the Query i.e. .json ?

 

Regarding (1), credentials are asked for in the New Source connection.  With (2), I am already logged into the URL API when connecting to the API and JSON file normally, but still do not get access when the file is on Share Point.  Perhaps there is a need for an API token to be placed in the the Query. I thought (3) might be the case also.

 

Others have found the same issue, but not in a situation with the JSON file on SharePoint:

 

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

 

Any thoughts would be appreciated.


Accepted Solutions
Moderator
Posts: 9,540
Registered: ‎03-10-2016

Re: Cannot connect to JSON file located on SharePoint

@Endeavor,

Please use SharePoint Folder connector instead.

Enter the site URL of your SharePoint in SharePoint Folder connector, if you are using SharePoint online, please use Microsoft account authentication, if you are using on-premises sharepoint, please use Windows authentication.
Capture.PNG


After loading data into Query Editor, then filter the extension to .json, click on Binary in Content column to expand data of the json file.
1.PNG





Regards,
Lydia

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

View solution in original post


All Replies
Moderator
Posts: 9,540
Registered: ‎03-10-2016

Re: Cannot connect to JSON file located on SharePoint

@Endeavor,

Please use SharePoint Folder connector instead.

Enter the site URL of your SharePoint in SharePoint Folder connector, if you are using SharePoint online, please use Microsoft account authentication, if you are using on-premises sharepoint, please use Windows authentication.
Capture.PNG


After loading data into Query Editor, then filter the extension to .json, click on Binary in Content column to expand data of the json file.
1.PNG





Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 2
Registered: ‎12-20-2018

Re: Cannot connect to JSON file located on SharePoint

Thank you Lydia. Smiley Happy