Log In
Register
Home
New Member
October 1, 2020
Offline
Hello everyone,
My advanced apologies if this was already answered somewhere, unfortunately, I could not find anything that solved my problem.
So, I have an excel file built up with power queries:
-> No internet needed to update the file;
-> No data extraction from other files, just local information on the file;
-> Microsoft Excel 365.
The problem is that the file takes about 20 seconds to run in my laptop and several others, but there are some others where it may take up to 5 min.
Even if I am only updating one of the queries with 15 lines (a query that is directly connected to an Excel table with not much calculation), I get the same loading time.
I already disabled "Allow data preview to download in the background".
Thank you.
July 16, 2010
Offline
Hi João,
Welcome to our forum!
Are all users on the exact same build of Excel for Microsoft 365?
Do all users have the same spec laptops?
Keep in mind that when you refresh queries, Power Query gets all the data again. It doesn't differentiate between 15 new rows or no new rows. In other words, there is no incremental refresh, just refresh all.
Mynda
New Member
October 1, 2020
Offline
Hi Mynda,
Thank you for your quick response and thank you for making some awesome web courses that I already had the chance to participate in (in this case through Udemy).
I don't know exactly what you mean by the "same build"? But same company, same sharepoint.
The laptops are different but with their characteristics, it shouldn't be a problem.
Are really all the queries refreshed even if you select one and refresh it? I am insisting on this because you can see the progress of one being refreshed and the others not.
I don't know if I can paste links on the forum but I made a dropbox link with the file: https://www.dropbox.com/s/9u2h......xlsm?dl=0
If you could take some spare minutes and look at it, it would be amazing.
Thank you,
July 16, 2010
Offline
Hi João,
I wasn't saying that all queries are refreshed when you refresh a single query, what I meant was when you refresh queries, whether that's a single one or multiple queries at the same time, each query that is refreshed will get all the data from the source again. It doesn't just get the new rows you added. I said this because you made the example that the queries are slow to refresh even if there are just 15 new rows of data. The point I was trying to make is that if the data was 100 rows before adding new data, then you add another 15 rows of data, Power Query will get all 115 rows of data again upon refresh, not just the 15 new rows. Hope that clarifies things.
When I asked if all users have the same build of Excel, I mean the same version and whether it's 64-bit or 32-bit. You can see what version they have in the File tab > Account > About Excel. The very first line (in green font) tells you the build number and whether they're on 64-bit or 32-bit. 64-bit should perform faster.
Thanks for sharing your file. I suspect the 'Encomendas_Pendentes' query is slowing things down, but it only took about 30 seconds to refresh all queries on my PC. I can't see anything obvious that you can change to improve things. It'd say it's slow because you are referencing/merging the queries multiple times.
You can try going into the query options (File tab > Options & Settings > Options) and under Global Data Load turning on 'Fast Data Load' and under Current Workbook Data Load turning off 'Background Data'.
Mynda
1 Guest(s)
