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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors