Power Query takes too long to load when connected into Sharepoint Files

[Anonymous]

Hello I'm intermediate using Power Query, and we are creating a dashboard in PBI, and for the data source, we need to use some data coming from SAP, these data is extracted daily with macros, saved into .txt files and then placed into a SharePoint folder. 

After that, I created a connection to that SharePoint, but the thing is that when retrieving the data, the loadtime takes too long, could be between 15 and 30 minutes, and if the internet connection is not good, it may take even more.  

We need four queries, one of them has 375K registers (POs), the other one 204K (STDC), and the other two have less than 100K.  

The larger table (POs) needs to be merged with the 2nd largest (STDC) to bring one field related to cost.  

I already applied some 'best practices' to improve the performance of the queries or to reduce the loadtime (which is what I want to achieve), like reducing the data set by removing all unnecessary columns from both tables, filtering out unnecessary registers and disabling data previews on the Background data. On the other hand, since the connection is made to .txt files and not a database, I think we cannot apply query folding in these cases. 

Even so, I'm having trouble with the load time and I wanted to see if there's an approach to solve this, like using Table.Buffer() which I'm not familiar with but I read something about it and if this may help in which step should I apply it, or the problem comes because of the connection to the SharePoint? 

How can loadtime be improved?  

Or does anyone have any better approach to solve this? 

Maybe we can try to solve that in Power Query in Excel instead of using PBI, I don't know if Power Query runs faster in Excel than in PBI.

Thanks for taking the time to read and any help would be greatly appreciated. 

[ImkeF · Accepted solution]

Hi @Anonymous ,
I have created some custom function for the import of files from SharePoint that reduce load time considerably if your SharePoint has lots of files already: Improve File Import from SharePoint in Power BI and Power Query – (thebiccountant.com)

[Anonymous]

Vielen Dank Imke! I went into your blog and saw the instructions and the video, it actually worked but in this case, the files are refreshed on a daily basis and the name of the download needs to have the date of the current date so the name will change every day. 

So for the Single File function, is there a way of setting a condition or to specify that it only takes a specific text?  

Before trying to apply your function, I was setting the connection to the SP folder with 'SharePoint.Contents' with the root path until the 5th slash as well, and then one step of Navigation and the 3rd step which gives me the specific file is the one that Filters by the name of the file, in this case: Text.Contains([Name], "EKKO")), I filter by that text and it gives me the file, no matter the date. 

Thanks in advance.