Power Query Speed - File Source Type

[filiparibeiro]

Hello.

Do queries run faster when the source is a local excel file instead of sharepoint excel files? If so why does this happen and is there a way to make it faster? Would table.buffer help?

Thanks in advance. 

[MatthRichardsUK · Accepted solution]

It is generally faster to work with data from a local Excel file rather than from a file stored on SharePoint, because the data does not need to be transferred over the network. The speed difference will depend on the size and complexity of the data, as well as the network connection between your computer and the SharePoint server.

Using the Table.Buffer function in Power Query can improve the performance of your queries by loading the data into memory, which can be faster than reading the data from the source each time it is needed. However, this may not always result in a significant improvement in performance, especially if the data is large or the query is already running efficiently.

There are a few ways you can try to improve the performance of queries that retrieve data from SharePoint:

1. Make sure the network connection between your computer and the SharePoint server is fast and stable.
2. If the data is large, try to optimize the queries to only retrieve the data that is needed, rather than loading the entire dataset into memory.
3. Consider using the Table.Buffer function to load the data into memory.
4. If the data is being used frequently, consider creating a local copy of the data and using that copy as the source for your queries. This can be done using Power Query's "Append" or "Merge" functions to combine the data from the SharePoint file with the local copy.