Forum Discussion
Slow
Hi everybody! Why is updating an query in excel power query the second time verry slow and the third time etc. faster? Why en how can i solve this problem?
2 Replies
- Caching: Power Query caches data after the initial load. The first time you run a query, Power Query has to fetch and process all the data from the source. On subsequent runs, it can use the cached data, which speeds up the process.
- Query Folding: If your data source supports query folding, Power Query can push transformations back to the data source, optimizing performance. The first run might involve setting up these transformations, while later runs benefit from the optimized queries.
- Data Source Performance: The initial query might be slow due to the performance of the data source or network latency. Once the data is cached locally, these factors have less impact.
- Complex Transformations: If your query involves complex transformations, the first run will take longer as Power Query processes each step. Subsequent runs can leverage the cached intermediate results.
You can optimize performance by filtering early, reducing the number of transformation steps where possible and choose the most efficient data connectors for your source.
Thank you very much for your quick and comprehensive response.
I have optimised my data and the queries in Power query as best I can. The problem is that on first refresh it takes about 10 seconds, but on 2nd refresh it sometimes takes 2 minutes or longer. On a 3rd time and then again faster towards 10 seconds.
My Excel file is in One drive and all but 1 query is loaded by tables from the same Excel file (i.e. not external). When refreshing the query, the message ‘connect to data source’ is shown. There is 1 query (bank) that fetches one or more files from a folder, but this query is not called in the other queries. I don't know if this ‘bank’ query is called in the background anyway and/or affects the other queries? I have tested removing this query and I notice no difference.
Are there any other conceivable solutions? Many thanks in advance for your thoughts! Greetings Bram
