Slow Power Query

[adraus]

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.

[jbwtp · Accepted solution]

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:

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

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