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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

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

4 REPLIES 4
OferNavi
New Member

I'm also having very slow performance with many basic Power Query.

after searching a solution widely, I see a lot of suggestion to check the internet connection and the files.

so I placed 8 small excel files locally- regular flat files, and the union/append operation takes 2-3 minutes (so every step after that also takes that time)

ETL tools supposed to be designed for multi steps operation, I've only encountered this malfunction in Power BI.

hope this is going to be optimized soon 🙏

edhans
Super User
Super User

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

@Anonymous 

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
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors