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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

SharePoint Folder Connector fails for API 15

Hello

 

I am using connector 'Sharepoint Folder' to connect to OneDrive4B folder. Last 3-4 days it is failing to get data either from Desktop or Service ( schedule refresh ).

 

The sharepoint site url is in the format of ( M editor ) "=SharePoint.Files("https://mydomain-my.sharepoint.com/personal/user_mydomain_com", [ApiVersion = 15])

 

The error is "Web.Contents failed to get contents from 'https://mydomain-my.sharepoint.com/personal/user_mydomain_com/_api/web/lists/getbyid('7eebe93e-0c36-43c6-bd49-502219e447bc')/RenderListDataAsStream' (500): Internal Server Error
Details:
DataSourceKind=SharePoint

 

 

When I change the API version from 15 (which is default ) to 14 is working again.

I guess an update might coause the issue.

 

Connecting to a sharepoint site is working - or a specific file in OneDrive4B , only when using above path to personal OneDrive

 

Can you please check?

Status: Needs Info
Comments
v-qiuyu-msft
Community Support

Hi @konstantinos,

 

From your description, it seems the issue happens when you get data from Onedrive for Business folder use SharePoint Folder with API version 15, right?

 

Based on my test in the latest Power BI desktop version 2.53.4954.621 64-bit (December 2017), get data from Onedrive for Business folder use SharePoint Folder with API version 15 successfully. See: 

 

e1.PNG

 

In your scenario, please update the Power BI desktop version to the latest version as ours then test again. 

 

Best Regards,
Qiuyun Yu 

 

Vicky_Song
Impactful Individual
Status changed to: Needs Info
 
konstantinos
Memorable Member

Hi @v-qiuyu-msft

 

Thanks for quick reply. You are correct, the issue is when I get data from Onedrive for Business folder using SharePoint Folder with API version 15.

 

I am using latest PowerBI Desktop and tried both standalone & store installs ( I have both ).

I have also these issue in different O365 tenants.

Might be an issue in Europe on OneDrive/Sharepoint & not PowerBI?

 

Version

PBI Version - A.JPG

 

 

Data Center Location 

 

 

o365 location.JPG

 

 

 

pbi store.JPG

 

Both API versions side by side

 

Compare 14 15 API.JPG

 

 

 

 

I am using this way to connect for years and is the first time I have an issue.

 

Let me know if you need more info.

 

Thank you

 

RoelVB
Helper I

It looks like this has something to do with the size of the site(collection). I had this problem with a large sitecollection (API 15 returned error 500, API 14 returned no files at all).

 

On smaller site(collections) everything works fine.

ovetteabejuela
Impactful Individual

@RoelVB, actually at this point your statement makes sense to me because I know the Sharepoint location where I am trying to connect to is now larger in size than before.

 

Hmmmm... I wonder what is the explanation for this

ovetteabejuela
Impactful Individual

FYI I have exactly the same experience, just today as how @konstantinos described it.

 

I also would like to add that the query I was having this error with, WAS working last week (with ApiVersion 15) and come weekend I've been getting emails about the 'Failed Schedule Refreshes'

 

Also, I was connecting to two SharePoint (personal) folders, but I am only getting the error from one of them the other one is working just fine.

 

This really needs an answer, at the moment I have to painstakingly edit ALL of my queries (and there's a lot!) and switch them to ApiVersion 14. Also it wasn't pointed out or maybe the OP did not experience it - but I am getting an extra 'error' row which demands that I have to add an extra step to delete.

fedlov
Advocate II

Same problem with powerquery in excel (office 365 and sharepoint)

 

Source = SharePoint.Files("https://mycompany.sharepoint.com/personal/FL/DS", [ApiVersion = 15]),

 

Worked fine until last week; now gives error 500; works fine again with API=14

Anonymous
Not applicable

Just voted it! Thanks for sharing workaround

chrismo
Regular Visitor

I have found the following link: https://community.powerbi.com/t5/Issues/SharePoint-File-Retrieval-Limit/idi-p/307021.

 

It explains that the absence of  [ApiVersion = 15] in the Advanced editor for SharePoint connection allows one to connect ~15,000 to 20,000 files. On contrary, the presence would limit the Power BI retrieval of SharePoint files to ~5000.

 

However, in the both of the above scenarios Power BI can take a LONG time to retrieve the data.

kkhatri
Regular Visitor

Hi, If you change [ApiVersion = 15] to [ApiVersion = "Auto"] it will automatically search for the best available option.