Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI'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 🙏
@Anonymous one other thing I noticed that can sometimes help performance is to uncheck this box in settings:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.