cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Unifydata
Frequent Visitor

Using Power Query editor causes CPU usage and Power Bi crash

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!!

PowerBI CPU.JPG

5 REPLIES 5
Unifydata
Frequent Visitor

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
Frequent Visitor

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!

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.



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Migasuke
Solution Sage
Solution Sage

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%.



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

V-pazhen-msft
Community Support
Community Support

@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.

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors