Power Query in Excel still using lots of CPU/Memory after query has finished refreshing

[Bella_1429]

Hello,

I have a ~3MB Excel file that contains about 16 Power Queries. These all feed into a single result query that gets loaded as a table to a spreadsheet.

When I click Refresh All, the query takes about 1-2 minutes to load the final data to the sheet. However, for several minutes afterwards, CPU usage by Excel will remain high, at around ~50-70%, and Excel will be semi-frozen, or at least difficult to interact with. Memory usage increases as well, but only by ~50MB. However, that takes much longer to go back to normal.

How come the process is still using extra CPU once the query has finished and loaded everything to the sheet? How can I troubleshoot this?

(Note that due to the recipient I can't load queries into Power BI, they need to stay in Excel.)

I did try disabling Background Refresh on the queries, but it doesn't seem to have solved the problem.

Thanks for any help! 

[PwerQueryKees]

Could it be that the sheet you load the result into, has time consuming formulas using the data you just loaded?
I am thinking of xlookup, countif, filter etc. 

If that is the case, then ALL formulas depending on ALL your loaded data are recalculated.

I have experienced this behaviour on a 2 tables table with each 10K rows refereiing to each other...

[Omid_Motamedise]

Do you load the result into the excel table, or just use it as connection table or upload it into Data Model?
If you load it into Excel, how many rows it is included?

[v-junyant-msft]

Hi @Bella_1429 ,

What version of Excel are you using? I can try to test it myself. Thank you.
I tested this with a file and query of similar size to yours in an existing version of Excel on my computer, and I did not have your problem. Here is my version:

And have you tried refreshing other queries on the same computer, and refreshing the same query on another computer to see if the same problem exists?
If your computer has no problem refreshing other queries, then it may be caused by the complexity of your query; if refreshing other queries will get stuck, and other computers have no problem refreshing, then it may be a problem with your computer.

Best Regards,
Dino Tao