Using Power Query editor causes CPU usage and Power Bi crash

[Unifydata]

Hi, i am having a constant issue with a few of my bigger reports.  When i open power Query to edit, memory grows and CPU usage hits 35-50 contantly, slowing the editor down to a crawl and eventally crashing it.

It has been happening for months, i'm on the latest Powerbi version. (June21)

I have all the load options disabled - parrallel, preview etc.

I don't have tracing on and i also disables all preview features.

I periodically clear the data and Q&A cache, close and reboot the computer

The report does have alot of queries, but i work on one, an it still just grinds to a halt.  If i copy and paste the query out, it is like lighting again and the CPU/crash issue doesn't occur.

i've attached my taskmanager - you can see massive memory, and high CPU but only in "PowerBI Desktop" not the mashup or Analysis server areas... so i have no idea what is happending

The query loads/refreshs fine, it is the editing that seems to bring everything unstuck

Any thoughts or suggestions on how i might fix it without a massive rebuild would be appreciated!!

[Unifydata]

An old thread, but found it googling for solutions. 

I was having a hell of a day with a large complex report with many (40+) power queries.  Referencing csv, xlsx, apis etc

After editing in power query for about 10minutes, memory and CPU would go nuts and the whole report/Power query editor would freeze up for minutes at a time. Completely unusable/unproductive

I just took your advice and deleted about 20 queries that i wasn't using (holding on to legacy stuff, a terrible habit!)

None were being refreshed, they were for Ron. (Later On) ... but obviously they were doing something in the background.... horrible things

Power Query editor is now back to usable, with minimal delays when working on multiple steps and queries... fingers crossed!

Thanks for the tip about the many queries.

FYI - i've got a i9 64GB mem machine, so it wasn't its fault.

[Unifydata]

Thanks for both of your replies!

@V-pazhen-msft, i've got a fairly new/powerful computer.  a recent i9 cpu and 64gb of ram, running the 64bit option.  I've tried the reinstall option etc to no avail

@Migasuke I think you might be correct regarding query steps - I was also pondering whether the Table.Buffer steps i use when sorting the dataset might be a cause, as it loads a lot to memory.  I will try and simplify things...

The only thing that doesn't make sense to me is that if i paste the query to a new powerbi file, there is no issue with editing/cpu usage/memory usage.  I feel like there is a memory leak somewhere!

[Migasuke]

Hi Again,

The only thing that doesn't make sense to me is that if i paste the query to a new powerbi file, there is no issue with editing/cpu usage/memory usage. I feel like there is a memory leak somewhere!

I observed the same and I have a theory for it, but it is hard to prove it:
PBI stores all steps with data in a RAM, sort of like a cache. The longer you work with PQ Editor, the more steps and data is stored and more memory is used. Also I think PBI is not very capable of using lot of memory, eventhough you have still free RAM.

[Migasuke]

Hi @Unifydata,

I used to have the same issue on my bigger reports. They consumed more than 6GB RAM and sometimes almos 100 % CPU (I have Gaming PC, so I was shocked)

After lot of testing and observing I came to following results:
1. Reduce number of your steps in Power Query. (If you have lot of duplicate actions, try to put them in one step).
2. Try to avoid "difficult" steps like merging, sorting on huge tables.
3. If you have the option, try to use different file source (for example if you are using XML, try to use CSV)

After following these steps I was capable to drop down memory app. to 4 GB and CPU barely hit 70%.

[Anonymous]

@Unifydata 

The CPU usage is normal but should not crash. It is difficult to know whether it is a software or hardware problem. Go through following:

1. The first step is always reinstall power bi desktop.

2. Does is happens on older version or it is your first time use Desktop on this machine?

3. How large is your data? Excess data amount could cause crash

4. Meet the Hardware requirement?

5. Did you use the correct version? 32/64 bit.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.