Cannot connect to JSON file located on SharePoint

[Endeavor]

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:

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:

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.

[v-yuezhe-msft · Accepted solution]

@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.



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.

Regards,
Lydia

[mikkuusi]

Hi, excellent solution! Most of my json files flow through nicely! But, I have a rather large file that fails to convert. Not sure why. When using the json import from desktop even that file works fine, but not when trying through the sharepoint way. It does load, but some fields are missing. Is it due to the 1000 row limit or something? How to avoid the error.

[Endeavor]

Thank you Lydia.