Loading .TSV from Sharepoint Folder is very slow

[StephenF]

The popup spins the loading wheel with evaluating... for sometimes more than an hour before rows are even pulled down from Sharepoint 365.

The file sizes add up to about 450 meg. I have a few conditional columns and group bys statements in the query code.

This is terribly slow compared to using local files on my machine.

Is onedrive any faster? or is there a way to get reasonable speeds of data processing and transfer with Sharepoint.

[edhans]

Two comments:

1. SharePoint can be very slow, but if is just one file, it shouldn't be a massive problem.
2. By default, your connection to SharePoint is SharePoint.Files() in the Source. Do this:
   1. Make a copy of your query.
   2. In the copy, delete everything after the source.
   3. Change SharePoint.Files() to SharePoint.Contents()
   4. Navigate to the folder where your TSV file is.
   5. Expand your TSV file.
   6. You'll need to now take the M code from your copied query and replace the code in your first query that does the same thing - which is you doing SharePoint.Files(), then navigating to your TSV file and expanding.

Alternatively, take all of your transformations from your first query and add it to the 2nd in the Advanced editor.

And in the future, unless you need to get to multiple files in different folders you should always change SharePoint.Files to SharePoint.Contents. It is much much faster, especially on sites with many files.

Last note: Consider using Azure DataLake to store text files. Much faster than Sharepoint no matter what code optimization you do. And dirt cheap. Pennies a month for hundreds and hundreds of MBs.

[StephenF]

l'm pulling multiple files, a new file is added to the sharepoint document folder each day.

Its very slow, when rows are coming down it's typically only about 2,000 per second.

[edhans]

---

@StephenF wrote:

l'm pulling multiple files, a new file is added to the sharepoint document folder each day.

Its very slow, when rows are coming down it's typically only about 2,000 per second.

---

Everything is in one folder, or multiple folders? If one folder, the SharePoint.Contents() adjustment will greatly help. Even if it is multiple folders, if it is a fixed number, I'd rather have 2-5 "SharePoint.Content()" queries that later are appended into one big query for further transformation than use SharePoint.Files() which scans EVERY SINGLE FILE on the site regardless of what you want.

That said, SharePoint will never match the speed of local files, OneDrive files, or Azure DataLake, which seems to be the fastest and most robust. It is why it was designed and why Power BI and Excel can get text and XLSX files from it directly.

[StephenF]

It's a variable number that increases by one per day.

I think I'll try onedrive and see if its any better then Sharepoint.

[StephenF]

Since onedrive seems to be the same as sharepoint as far as storage I am at an impasse.