Hi,
I’ve been having some performance issues with data loading in Excel’s Get & Transform.
I can’t go into specifics on the query or post the formula for privacy reasons, but to give a brief overview: the amount of data I’m performing on is fairly low (maximum 36,000 rows of data and at a max 4MB of data); the sources I’m using consist of 3 from network drives and 3 from Excel files; the query itself is divided up into 1 “master” queries; a secondary query which “feeds” that master queries and 2 more preamble queries which takes incompatible data out and feeds it back into to both of those queries.
So, the structure is something like 6 sources > 2 preamble > 1 secondary > 1 master
In all these queries I perform multiple merges and appendages between these sources to cross-reference and compile the data; and on the sources multiple replacements of data to systemize it.
Currently, as I’m writing this, the query is taking over an hour to refresh or just getting stuck in an endless spinning cycle before crashing. I believe this is in part due to the low-spec PCs our organization uses: a 2/4 C/T and 4GB Lenovo laptop, which is constantly running at ~100% utilization across CPU, RAM and Disk.
Previously using the methods and links discussed here:
https://www.myonlinetraininghub.com/excel-forum/power-query/any-way-to-speed-up-really-slow-refresh-...
I’ve gotten the refresh time down to 10 minutes. However, whenever I need to make a change or amendment in the editor it still takes a long time to load.
For full disclosure, I’ve tried:
• Loading / Unloading from a data model – I never got a clear answer on whether it would improve performance to load the data into a Data Model, however I ended up doing so, as not doing so seemed to cause a few crashes.
• Utilizing Table.Buffer at the beginning of queries – It didn’t seem to make much difference
• Loading (some) Excel sources as static tables in the spreadsheet – seemed to help
• Restructuring multiple queries into one – helped reduce to 10 minutes, but still causing issues.
• Turning off background refresh as recommended in link – Helped load but impacted on editor’s performance.
So, I’m just wondering if there’s anything more I could try to get this the load times down.
