SharePoint Folder Connector fails for API 15

[konstantinos]

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?

[v-qiuyu-msft]

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: 

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

Best Regards,
Qiuyun Yu 

[konstantinos]

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

Data Center Location 

Both API versions side by side

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]

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]

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

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]

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]

Just voted it! Thanks for sharing workaround

[chrismo]

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]

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