cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Power Query Editor Steps are painfully slow

I am using power query editor in power bi with many (like 30) different queries.

Now every step in a query takes literally ages to load in preview.

Even If I go on the very first query step, which is only the source extraction from a SQL Server via Native Query and I consider  a simple query with no merge or append, it takes up to 5 minutes to load the 1000 rows preview. Why is this and how can I fix this since it is impossible to work with. If I try the same query in a new and fresh .pbix it loads in miliseconds as expected. What exactly is happening?

 

Greetings

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Uncheck all the settings where Power Query tries to mess with the meta data (column type inference, relationship detection etc.) Uncheck anything you don't want Power Query to do for you.

View solution in original post

5 REPLIES 5
CortadoJ
Frequent Visitor

I'm getting impossibly slow response when changing small things in power query.
Does anyone know of a list of these "Settings where Power Query tries to mess with..."?
I would turn off the preview but it's ticked and greyed out. 😞
Any help appreciated. 

Hi @CortadoJ ,

Try doing the below items. This should help improve performance

1. Disable the Auto date/time under Time intelligence

2. Turn Off Auto detect relationships under Relationships in Data Load section

3. Replace any duplicate queries in your report with Query references and include only the differing steps to the Query reference

4. Remove any unwanted Columns from the dataset. (Wide Table hugely slow down the performance)

5. Disable loading of any unwanted queries during refresh

 

Regards,

 

If this helps, mark it as solution and appreciate with a Kudo!!

Careful with #4 - that is a very sharp double edged sword, especially in scenarios with query folding and poorly performing data sources.  In such scenarios a full table spool is often faster than hand-picking columns.

Show a screenshot of your options page current file/report settings.

Try to avoid doing table merges in Power Query. Join the tables in the data model.

lbendlin
Super User
Super User

Uncheck all the settings where Power Query tries to mess with the meta data (column type inference, relationship detection etc.) Uncheck anything you don't want Power Query to do for you.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.