Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Endeavor
Regular Visitor

Cannot connect to JSON file located on SharePoint

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.

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@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

2 REPLIES 2
v-yuezhe-msft
Employee
Employee

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

Thank you Lydia. Smiley Happy

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors