Power Query Editor doesn't load even small tables and occupies ressources

[Anonymous]

Hello everyone,

we're having massive performance problems with the Power Query Editor. It was always a bit slow to perform some tasks like adding a new column, but it worked until yesterday. The query editor runs stable and pretty fast until i select a table. The query editor displays the yellow dots to indicate that it's loading but it just takes forever and i never see my data thus i can't add columns

Further, independent of this issue, PowerBI takes up to 11GB of my RAM and 70% of the CPU with the "Microsoft Mashup Container" 3 times pulling the most CPU%, and the "Power BI" Container getting the most RAM. 

A colleague managed to see the tables after 4 hours of loading and a very limited performing PC.

Even with disabled background refresh and targeted loading of a table with only 12 rows it still takes indefinitely.

Is there any way to debug and see whats affecting the performance this much? Even if we found the Issue, how do i Optimize without being able to delete colums etc.?

I would appreciate any help, Thank you in advance!

We're importing the .pbix file from the PowerBI web Version. Its size is around 60 mb. 

specs: i5-8250U, Intel UHD Graphics 620, 16 GB RAM DDR4-2400

[Anonymous · Accepted solution]

Hi everyone,

sorry for the late reply, i was hesitating to post my code because of Colleagues Names in the calculated Columns. We fixed the issue by ourselves, by building again.

Solution:

Many of our Tables had many Steps (30+): So we grouped them. (I think that was the biggest factor. Now each function has only to be called once?)

Much data was unnessecarily loaded: We already filtered in the Dataflow and used the Query editor only for modeling

If you come upon this on your search i can only emphasize on grouping your steps in the Query editor and using the reference option if possible when "copying" a table. And be careful on deleting steps that later steps could rely on. Guy in a Cube also has some helpful content regarding performance issues.

Thanks everyone for all your help,

Best regards

[v-kelly-msft]

Hi  @Anonymous ,

You could use performance analyzer to check about it and pay attention to the options which cost long duration:

For the reference,pls check below:

https://docs.microsoft.com/en-us/power-bi/guidance/monitor-report-performance

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Can we have a look at your code?