Power Query SharePoint.Files() works but SharePoint.Contents() can't authenticate...

Question

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.

Answer 1

@Pearce, Brent ,

Power Query is currently not supported in Microsoft Q&A.

You can post your question in Microsoft Power Query Community. The experts there will be willing to provide decent help for your question.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

I apologize for misfiling. No idea how I got to the Q&A forum...