Yes, you can speed up Power Query refresh in Excel by reducing data load, using native database queries instead of pulling full tables, disabling background refresh, and avoiding unnecessary steps. The biggest gains usually come from filtering data at the source and removing steps that expand or merge large tables unnecessarily.
Why Power Query Refresh Can Be Slow
Power Query (officially called Get & Transform Data) loads data into an internal engine, then applies transformation steps. Each step is evaluated when the query refreshes. The most common performance killers are:
- Loading entire tables from SQL, Excel, or CSV files when you only need a few columns.
- Using
Table.Bufferin M code excessively, which can increase memory usage. - Performing merges and appends on large unindexed data.
- Having many queries that reference each other, causing repeated recalculation.
- Enabling background refresh in Excel options, which can slow down other operations.
How Power Query Refresh Works
When you click Refresh, Power Query re-evaluates the entire query pipeline from the source. It does not store a cache of the final result between refreshes (unless you load to the data model and use DirectQuery). The engine is column-oriented and tries to push operations down to the source where possible, but not all connectors support this. For example, SQL Server queries can be folded (meaning the SQL is sent to the server), but many Excel file steps cannot be folded, so the data is pulled locally.
Best Practices to Speed Up Refresh
Apply these in order of impact:
- Reduce columns and rows at the source. In SQL, write a query that selects only needed columns and filters rows with WHERE. In Excel, use Table.SelectColumns and Table.SelectRows early.
- Disable background refresh. Go to Data > Queries & Connections > Queries tab > right-click a query > Properties > uncheck “Enable background refresh”. This prevents Excel from refreshing in the background and competing with other tasks.
- Use native queries instead of importing entire tables. In the Power Query editor, when connecting to SQL Server, choose “Advanced” and paste a SELECT statement. This allows query folding.
- Remove unnecessary steps. Each step adds overhead. Delete steps that are not used or that duplicate earlier transformations.
- Combine files at the folder level. If you import many files, use the “Combine Files” feature from a folder, which uses a sample file to define the transformation, rather than loading each file individually.
- Use the Data Model (Power Pivot) instead of loading to a worksheet. Loading to the data model is often faster because it uses compression and can handle larger data sets.
- Sort and filter before merging. When merging two tables, filter both to the smallest possible size first.
Common Mistakes That Slow Refresh
Many users inadvertently slow down queries by:
- Using
Table.Combineon hundreds of sheets instead of using a folder source. - Expanding columns with lists or records (like from JSON) that multiply row counts.
- Adding a step that changes data types on entire columns after a merge, causing the engine to re-evaluate.
- Not using query folding. If you see a “Query Folding” indicator in the query diagnostics, you can check if steps are folded. Non-foldable steps force local processing.
- Refreshing all queries when only one is needed. Use right-click > Refresh on a single query.
Using Query Diagnostics to Find Bottlenecks
Excel has built-in diagnostics. In the Power Query editor, go to Tools > Diagnostics > Start Diagnostics. Then refresh the query. After it finishes, stop diagnostics and review the report. Look for steps with high “Duration” or “CPU” values. Often, a step that expands a column or changes data types is the culprit. You can also see whether a step is folded or not (the report shows “Folding” status).
When to Use Power Query vs. Power Pivot vs. VBA
Power Query is best for data transformation and cleaning. Power Pivot (the Data Model) is better for large data sets and relationships because it uses in-memory compression and can handle millions of rows. VBA is not recommended for data transformation because it is slower and harder to maintain. If you need real-time data, consider using Power Pivot with DirectQuery (for SQL Server) or using a live connection to a database. For very large data, consider using Power BI instead of Excel, as it has a more efficient engine.
Frequently Asked Questions
Why does my Power Query refresh take so long?
Usually because you are loading too much data or have non-foldable steps. Check if your query is pulling entire tables from a database. Use a native query to filter at the source. Also, remove any steps that expand columns or add calculated columns that are not necessary.
How do I disable background refresh in Power Query?
Go to Data > Queries & Connections. In the Queries pane, right-click the query and choose Properties. Under the Usage tab, uncheck “Enable background refresh”. This forces the refresh to run in the foreground, which can speed up the overall process because it doesn’t compete with other Excel operations.
Does loading to the Data Model speed up refresh?
Yes, often. Loading to the Data Model (Power Pivot) uses a columnar compression and can be faster than loading to a worksheet, especially for large data sets. To do this, in the Load To dialog, select “Only create connection” and then add to the Data Model.
What is query folding and why does it matter?
Query folding is when Power Query translates your transformation steps into a single query that the source database can run. For example, if you filter rows in Power Query on a SQL Server source, the filter is pushed to the SQL query. This reduces the amount of data transferred to Excel. If a step is not foldable (like adding an index), it forces local processing, which is slower.
Can I use Power Query to refresh automatically without opening Excel?
No, Power Query only runs inside Excel. You can schedule a refresh using Power Automate (with a premium connector) or by using VBA to open the workbook and refresh, but that still requires Excel to be running. For fully automated refresh, consider Power BI or a scheduled data refresh in a database.
Why does my refresh get slower after adding a merge step?
Merging two large tables can be slow, especially if you are merging on non-unique columns. To speed it up, sort both tables by the merge key before merging, and filter both tables to the smallest possible size. Also, after merging, expand only the columns you need, not all columns.
Related
- Excel How to Select Certain Cells: 6 Quick Ways
- Excel Transpose: How to Quickly Flip Data
- How to Rename a Cell in Excel: A Complete Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Repeatedly Not Responding? Causes and FixesIf Excel repeatedly shows “Not Responding,” first give it a short time to finish, especially if a large…
- Excel Data Table Relationships and How to Create ThemIn Excel, table relationships link separate tables through matching key columns, letting PivotTables and the Data Model analyze…
- Google Sheets Weekly Timesheet Template: Setup TipsYes. You can make a weekly timesheet template in Google Sheets by creating a table for each day,…
- How to Close the Research Pane in Excel on WindowsTo close the Research pane in Excel for Microsoft 365 on Windows, click the X at the upper-right…
- Consolidate Rows in Excel Based on CriteriaTo consolidate rows in Excel based on matching criteria, create a PivotTable: select the data, choose Insert >…
- How to Add a Footnote in a Word Document, Step by StepTo add a footnote in Microsoft Word, click where its numbered reference should appear, then select References >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.