High CPU usage in Excel after query refresh

[Anonymous]

Anyone else seen this.

I had a simple query running just fine.  Then added a piece of coding (fgrom this forum) to fill blank rows in a column based on a criteria from another column.  Coding was from this forum.  

On adding this, the refresh time went up to 90 minutes+ and has become pretty much unusable as to interrupt it I have to shut down Excel with Task Manager.  Having looked into it, it would seem that opening Excel is just fine (watching the CPU usage) but as soon as I hover over any query (there are 4 simple ones) the CPU usage jumps to 30-40% initially and then higher before the preview window refreshes or even the Query Editor window opens.  Even closing the query editor window the CPU usage continues at 80%+ as far as I can tell 'forever'

I've tried even repairting Excel this afternoon with no better results.

This simple query is now unusable and even attempting to delete the inserted step just jams Excel.  At some opoint in the processing there appears a 'hidden' dialog box where you have to use Alt+Tab to select is and then pressing ENTER opens a box looking for a fileint the System32 folder......

Anyone any ideas please ??

[edhans]

There are not enough details to diagnose this issue. The code provided in this form cannot be malicous so that isn't it. You can have some code that works great for 10,000 to 1,000,000 records, but that same code can be extremly inefficient at 10,000,000 records.

One thing you can try is to disable all of the Column Profile, Column Distribution, and Column Quality features in Power Query on the View tab. Those all cause additional processing as Excel is evaluating data to return those charts. Very helpful, but I disable if I am working on a complex step or a lot of slow data (CSV files).

Barring that, your best bet may be to just restore to a previous version in OneDrive/OneDrive for Business/SharePoint.

[Anonymous · Accepted solution]

Thanks edhans,

There are only 2700 rows, but I have seen a possible lead from a similar question I has about filling the rows, from where I got the original code segment  :  https://community.powerbi.com/t5/Power-Query/Filling-blank-values-in-a-dataset-based-on-a-shared-val...

Going to look at this.

Thanks again