SharePoint.Files() works but SharePoint.Contents() won't authenticate

[bpearce]

Recently migrated hundreds of Windows shares to SharePoint Online. This forced us to modify any processes that used Power Query to read data from Windows to now read data from SharePoint. Most of the time this was easy to accomplish - however - there are some SharePoint sites that we are struggling with. Imagine if you created a new query to read files from a SharePoint folder. When building this via the Power Query GUI, the underlying formula used is SharePoint.Files(). While this works for sites with a few hundred files, performance drops dramatically/exponentially for sites with more than a few hundred files. Fortunately the function SharePoint.Contents() performs much better and is almost interchangeable with SharePoint.Files() - even the function's parameters are the same. So typically we use the GUI to build the initial query, then change SharePoint.Files() to SharePoint.Contents(). Problem is that for about 10% of the sites we get an authentication error when we switch to SharePoint.Contents(). We can't seem to get past the error. If we change it back to SharePoint.Files() it authenticates just fine - but performs poorly for large sites. Again, we make no changes to the parameters for these functions. Anyone else experience this behavior? Any guidance would be greatly appreciated.

[bpearce]

@Daryl-Lynch-Bzy - I appreciate your comments. I have tried Table.Buffer() is some ill-performing queries but with limited success. I have not tried Binary.Buffer() but will give it a look. For the original question though it is not much help as I can't get past the SharePoint.Contents() function as it errors out on authentication.

FWIW - Since my original post I have tried SharePoint.Contents ("mysite",APIVersion = 14) and got past the authentication issue, but general performance is on the same order as SharePoint.Files(). 

[andyoliver]

I have exactly the same issue here.

But... a few users in my organization are able to use SharePoint.Contents without any problems. 

Other users are not able to use SharePoint.Contents due to the authetication problem you mentioned.

I still could not determine why this works with a few users. Different Sharepoint priviledges?

EDIT: Yes, I confirmed that different Sharepoint priviledges makes the whole difference.
I asked to change temporarily my priviledge to "site owner" and now SharePoint.Contents works flawlessly.

[RafaelReis]

Hello! I'm having the exact same issue. Do you get past from the Sharepoint Owner limitation?

[Daryl-Lynch-Bzy]

Hi @bpearce, I use SharePoint files as a data source for several Workspaces, so I have come across the issue that you have encountered.  One example open over 500 files twice.  Once to read the date, and second time to read the data.  In this situation, I am using SharePoint.Contents in a Dataflow backed by "EM1" capacity.  Each file is read from it Binary using a Custom Function.  I don't use the "Expand Table" features.  The input to the custom function is the Binary file.  I then use the Binary.Buffer function before the Excel.Workbook function.