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

Power Query Editor slow - renaming alone takes 5 minutes

In Power BI Desktop:

I am using import mode and doing some data transformations in the Power Query and everything takes sooo long. Simply renaming one of the "Applied Steps" results in the preview refresh that takes 5 minutes.

 

Is this settings issue? As we have with excel to switch-odd automatic refreshes.  

 

I am just really confused 🙂 P.S. I am connecting to remoted db via VPN and internet.

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

It can be slow unfortunately. It depends on how fast your connection is, how your transformations are happening, etc. If you have a few hundred thousand rows in Excel or a CSV file, doing an initial filter, renaming, etc. goes fast. But if you do merges, groupings, etc. then that requires Power Query to load more and more data to do the work.

 

Make sure that you have told Power Query to only give you column profiling on the first 1,000 rows. That is the default, but if you changed it it will slow things down considerably. It is in the lower left corner of the app.

edhans_0-1610560531064.png

One thing you can do that sometimes helps is buffer previous statements. So after a grouping, wrap the entire thing in Table.Buffer() in the formula bar, or List.Buffer() if it is a list. You can remove them all later if desired once you are done coding.

You can also rename steps faster in the Advanced Editor, but you need to know what you are doing. Unlike doing it in the US, the advanced editor will not automatically change subsequent steps that relied on the first name. You have to manually fix those too, or the code simply breaks until you do fix it.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User III
Super User III

@Dovilekr one other thing I noticed that can sometimes help performance is to uncheck this box in settings:

edhans_0-1611267865136.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
V-pazhen-msft
Community Support
Community Support

@Dovilekr 

Many reasons can slow the performance, e.g. datasource connection, Internet speed, complex data shaping and transformation, etc . It is unlike to reach a specific solution of how to improve the performance speed for a model. Optimization is a huge and deep topic.I would suggest you to start with some tips in Power Query.

Performance tip to speed up slow pivot operations in Power Query and Power BI (thebiccountant.com)


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

edhans
Super User III
Super User III

It can be slow unfortunately. It depends on how fast your connection is, how your transformations are happening, etc. If you have a few hundred thousand rows in Excel or a CSV file, doing an initial filter, renaming, etc. goes fast. But if you do merges, groupings, etc. then that requires Power Query to load more and more data to do the work.

 

Make sure that you have told Power Query to only give you column profiling on the first 1,000 rows. That is the default, but if you changed it it will slow things down considerably. It is in the lower left corner of the app.

edhans_0-1610560531064.png

One thing you can do that sometimes helps is buffer previous statements. So after a grouping, wrap the entire thing in Table.Buffer() in the formula bar, or List.Buffer() if it is a list. You can remove them all later if desired once you are done coding.

You can also rename steps faster in the Advanced Editor, but you need to know what you are doing. Unlike doing it in the US, the advanced editor will not automatically change subsequent steps that relied on the first name. You have to manually fix those too, or the code simply breaks until you do fix it.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors