Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
adraus
Frequent Visitor

Slow Power Query

I want to make a simple edit to a calculation in Power Query.  I click on the step where I want to insert my new custom column - and it takes literally half an hour before I can enter the edit!  What's the deal??

 

I have many steps in this table (~60).  Is this the problem??  But I need these calculations!

It's not the later steps; I could click on any step in the list and get this problem.

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable Member

Hi @adraus,

 

I understand your frustration, however, as it was mentioned many times on this forum, the code does run slow sometimes and it can be caused by an array of reasons (even worse, it is usually caused by more than one factor).

 

E.g. if you have 5m rows in the table it will inevitably be quite slow to refresh. Then it can be incorrect table merging, I saw examples where a 1000 line table was loaded about 1m times because of the query structure. This can be sorted/patched in most cased using Table.Buffer

 

Pivoting and unpivoting actions (as well as joins//merges) are quite slow, and the time required to turn them around grows exponentially with the size of the table. This can be sometime resolved by moving the pivoting unpivoing into a Table.Group to reduce the in-memory footprint or, for joins, it can be replaced by Table.Combine with certain techniques.

 

Overall, 60 steps in the table is huge and, by default, the table does not stop re-calculating at the step that you selected. This behaviour can be changed in the Options:

jbwtp_0-1665452906633.png

In the Data Load section remove the flag for "Allow data preview in the background":

jbwtp_1-1665452969444.png

This has its own cons and most time is quite inconvenient, but it makes the table recalculate less (which is what you want working with large and slow queries).

 

If you want more help, I guess, we need more details regarding the table (size: rows/colums) and the query itself.

 

Hope this helps, 

Cheers,

John

 

View solution in original post

1 REPLY 1
jbwtp
Memorable Member
Memorable Member

Hi @adraus,

 

I understand your frustration, however, as it was mentioned many times on this forum, the code does run slow sometimes and it can be caused by an array of reasons (even worse, it is usually caused by more than one factor).

 

E.g. if you have 5m rows in the table it will inevitably be quite slow to refresh. Then it can be incorrect table merging, I saw examples where a 1000 line table was loaded about 1m times because of the query structure. This can be sorted/patched in most cased using Table.Buffer

 

Pivoting and unpivoting actions (as well as joins//merges) are quite slow, and the time required to turn them around grows exponentially with the size of the table. This can be sometime resolved by moving the pivoting unpivoing into a Table.Group to reduce the in-memory footprint or, for joins, it can be replaced by Table.Combine with certain techniques.

 

Overall, 60 steps in the table is huge and, by default, the table does not stop re-calculating at the step that you selected. This behaviour can be changed in the Options:

jbwtp_0-1665452906633.png

In the Data Load section remove the flag for "Allow data preview in the background":

jbwtp_1-1665452969444.png

This has its own cons and most time is quite inconvenient, but it makes the table recalculate less (which is what you want working with large and slow queries).

 

If you want more help, I guess, we need more details regarding the table (size: rows/colums) and the query itself.

 

Hope this helps, 

Cheers,

John

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors