Yes, Excel Power Query can slow to a crawl during refresh, and the most common causes are loading unnecessary columns, refreshing entire tables instead of incremental ranges, and using inefficient query steps. The fixes below address each of these directly and will typically cut refresh time by 50–90%.
Why Power Query Refresh Slows Down
Power Query loads data into memory, transforms it, and then pushes the result into an Excel table or the data model. Every step in the query editor creates an intermediate table in memory. If you import a 1-million-row CSV and then filter it, Power Query still reads and holds all 1 million rows before applying the filter. The same applies to merging queries: a merge between two large tables can create a huge cross-product internally, even if the final output is small.
Another major factor is the source itself. Refreshing from a web API, a SharePoint folder, or a SQL view that runs a heavy query will always be slower than refreshing from a local Excel file. Network latency, authentication handshakes, and server-side load all add seconds or minutes to every refresh.
How Power Query Refresh Works Under the Hood
When you click Refresh, Excel re-evaluates the entire query pipeline from scratch. It does not cache intermediate results between refreshes. Each refresh performs these steps in order:
- Connect to the data source and retrieve the raw data.
- Apply each transformation step in the query editor sequence.
- Load the final table into the worksheet or the data model.
- Recalculate any formulas that reference the loaded table.
Power Query uses lazy evaluation in some cases, meaning it may not fully materialize every step if the next step doesn’t need all columns. But in practice, most queries force full materialization because of sorting, grouping, or merging. The Table.Buffer function can force an in-memory snapshot, but it only helps if you reference the same table multiple times; otherwise it adds memory overhead.
Top Causes of Slow Refresh (and How to Fix Them)
Here are the most frequent culprits, listed in order of impact:
- Loading all columns. In the query editor, right-click the final step and choose Remove Columns to drop columns you don’t need. Fewer columns mean less memory and faster refresh.
- Refreshing the entire table when you only need new rows. If you append new data daily, use Append Queries and then remove duplicates, but better yet, use Power Query’s incremental refresh (available in Power BI, not in Excel desktop). In Excel, you can split data by date and only refresh the latest partition.
- Using
Table.Distincton a large table. Distinct requires sorting or hashing, which is O(n log n). If you only need to remove exact duplicates, consider grouping by key columns first. - Merging queries without pre-filtering. Filter both sides before merging. For example, if you merge Sales and Products, filter Sales to the last 30 days and Products to active items first.
- Using
Web.Contentswith a full page download. If you scrape a webpage, the entire HTML is loaded. UseWeb.BrowserContentsonly when needed, and prefer APIs that return JSON or CSV. - Loading to a table instead of the data model. A worksheet table has more overhead than a data model table. If you don’t need to see the data in cells, load to the data model only (choose Only Create Connection and then check Add to Data Model).
How to Diagnose Which Step Is Slow
Power Query has a built-in diagnostic tool. In the query editor, go to Tools (on the Home tab) and select Diagnostics > Start Diagnostics. Then run a refresh. After it finishes, go back to Tools > Diagnostics > Stop Diagnostics. A new query appears with timings for each step. Look for steps with high Duration values. Common offenders are Source (network), Navigation (parsing), and any Table.Join or Table.Group steps.
If you cannot use diagnostics (older Excel versions), add a temporary step that records the current time using DateTime.LocalNow() and then subtract later. But the built-in tool is far easier.
Practical Optimization Steps That Actually Work
These changes require no coding and give immediate results:
- Disable background refresh. Go to Data > Queries & Connections > right-click your query > Properties and uncheck Refresh data when opening the file and Enable background refresh. This prevents Excel from refreshing while you are working, which slows down both the refresh and your interaction.
- Use
Table.SelectColumnsearly. In the query editor, after the source step, add a step that selects only the columns you need. This is faster than removing columns later because it reduces the row width early. - Change data types at the source. If a column is numeric, set the type in the source step (e.g.,
Table.TransformColumnTypes) rather than letting Power Query guess. Guessing can cause extra scans. - Use
Table.Bufferafter a sort or filter. If you reference the same query multiple times in merges, buffer it once. But be careful: buffering a large table uses memory and can slow down if you don’t reuse it. - Split your query into smaller steps. Sometimes a single complex step is slower than two simpler ones because Power Query’s optimizer can’t parallelize. Test by breaking a merge into a filter then merge.
Comparing Power Query Refresh to Other Methods
Power Query is slower than a direct connection to a database view for two reasons: it loads data into Excel’s memory, and it applies transformations in a separate engine. If you only need to display data without heavy transformation, consider using a PivotTable connected directly to the database (via ODBC) instead of Power Query. That bypasses the query engine and often refreshes faster.
However, Power Query is faster than manual copy-paste or VBA loops for large datasets. VBA that loops cell-by-cell is notoriously slow; Power Query’s column-oriented operations are optimized in C++ under the hood. For a 100,000-row dataset, Power Query refresh might take 5 seconds, while a VBA loop could take minutes.
When to Consider Alternatives
If you have already optimized and refresh still takes over a minute, consider these alternatives:
- Power Pivot (data model) with DAX measures. Instead of loading a flattened table, load normalized tables and create measures. DAX calculates only the aggregates you need, which can be much faster.
- Power BI Desktop. It has incremental refresh policies and a column store engine that is faster for large data. You can still use the same Power Query steps.
- Use a database view or stored procedure. Push the heavy transformation to the server. Then Power Query just imports the result.
- Use Excel’s native Get & Transform with a connection-only query. If you don’t need to see the data, load to the data model only.
Frequently Asked Questions
Why does my Power Query refresh take longer on the second refresh?
It shouldn’t, unless your source data grew. But if you have background refresh enabled, the second refresh might run simultaneously with other queries, causing contention. Also, if you added a new step that sorts or merges, the first refresh may have been cached by the OS, while the second one reads from disk again.
Does disabling the ‘Load to worksheet’ option speed up refresh?
Yes, significantly. Loading to the worksheet creates an Excel table with formatting, conditional formatting, and cell references. Loading only to the data model avoids that overhead. You can still use the data in PivotTables.
Can I use incremental refresh in Excel?
No, incremental refresh is only available in Power BI Premium or Power BI Pro. In Excel, you can simulate it by splitting your data into date partitions and refreshing only the latest partition using a parameter that changes the source folder or file path.
Is it better to use a CSV or an Excel file as the source?
CSV is usually faster to read because it is plain text and has no formatting or metadata. Excel files (.xlsx) have compressed XML that takes extra time to parse. If you control the source, export to CSV.
Why does my query refresh slowly only when the file is opened?
Excel refreshes all connections in the order they appear in the Queries & Connections pane. If you have many queries, they run sequentially by default. You can change this by going to Data > Queries & Connections > right-click a query > Properties > Refresh control and uncheck Refresh this connection on file open. Then refresh manually or via a macro after opening.
Does using Table.Buffer always speed up refresh?
No. Buffering forces Power Query to materialize the table in memory immediately. If you use it once and then discard the table, it adds overhead. It only helps if you reference the same query multiple times (e.g., in multiple merges). Use it sparingly and only after testing.
Related
- Excel First Letter Uppercase Rest Lowercase?
- Excel Eye Center Provo: Services, Doctors & What to Know
- Excel: Count Cells with Specific Text
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Color-Coded Drop-Down Lists: Setup and RulesTo make a color-coded drop-down in Excel for Microsoft 365 on Windows, create a list with Data Validation,…
- Outlook Server-Side Rules: How to Create ThemCreate a server-side rule in Outlook by opening your mailbox’s rules settings, choosing the messages to match, and…
- Excel Equivalent to Google Sheets QUERY and How to Use ItExcel has no single built-in function that works exactly like Google Sheets’ QUERY. For a one-off filtered or…
- Compare Two Open Documents in Word: A Step-by-Step GuideTo compare two open documents in Microsoft Word, save both files, then choose Review > Compare > Compare…
- Excel Slicer Font Size: What You Can and Can’t ChangeIn Excel for Microsoft 365 on Windows, a slicer has no ordinary Font Size box on the ribbon.…
- INDEX MATCH in Google Sheets: Search Another SheetYes. In Google Sheets, INDEX MATCH can look up a value on another tab in the same spreadsheet…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.