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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

10 REPLIES 10
Robert_Lynch
New Member

I am running a import using get from folder and it has MANY files to bring in.  They are global files and I need to bring down to my specific lines to import.  I created a filter using the below syntax where ETF_List is the list that I want to use to filter down where the Symbols match.

 

My issue is using this is EXTREMELY slow.  Is there way to speed this up?

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each (List.Contains(ETF_List,[Symbol]) = true))

Use List.Buffer on your list, and experiment with indexing the [Symbol] column, or using Table.Buffer on the files once they are read into tables.

@lbendlin  what would that syntax be?  I am new to power queries.

LB = List.Buffer(ETF_List),

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each List.Contains(LB,[Symbol]))

Anonymous
Not applicable

Same issue here. I had 13 columns and 635 rows all pulling from a table within workbook. It took a few minutes just clicking between already applied steps. I deleted all named ranges but first, I had to unhide all hidden ones. To do that you need a bit of VBA code which I found online (https://excelhelphq.com/how-to-unhide-or-delete-name-ranges-in-excel/).

 

Alt+F11 -> Insert -> Module then paste in following code:

 

Sub ShowAllNames()

Dim n As Name

For Each n In ActiveWorkbook.Names

n.Visible = True

Next n

End Sub

 

Close the window and press F5 to run the macro. Close the VBA editor. Go to Formulas in the ribbon and select Name Manager from Defined Names section. Delete all the carp from here. Bear in mind that all you tables, print areas, fitlers etc. are also saved here so be carefull deleting everything.

 

Hope this will work for you too. 

Anonymous
Not applicable

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

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.