A merge that used to take a few seconds now sits there spinning for ten minutes, or times out entirely. Nothing about your steps looks wrong. The columns match, the join type is sensible, and yet the refresh crawls or fails.
The good news is that Power Query merge slowness has a fairly small set of real causes, and most of them trace back to one thing: Power Query is downloading far more data than it actually needs to. Once you understand why that happens, the fixes are mostly mechanical.
Quick Answer: What's usually causing this?
In most cases, query folding has broken somewhere before your merge step, so Power Query is pulling every row from both tables into memory instead of letting the source database do the join efficiently. Check whether folding is still active right before the merge (right-click the step, look for "View Native Query"), confirm your join key columns have matching data types, and trim both tables down to only the columns and rows you actually need before merging.
On this page
- Why Power Query Merges Get Slow in the First Place
- Step 1: Check Whether Query Folding Is Still Active
- Step 2: Reduce Columns and Rows Before You Merge
- Step 3: Match Data Types on Your Join Keys
- Step 4: Reconsider Your Join Type
- Step 5: Push the Join to the Source Instead
- Step 6: Use Table.Buffer Selectively
- Other Fixes Worth Trying
- Quick Diagnostic Flow
- Key Takeaways
- Frequently Asked Questions (FAQs)
Why Power Query Merges Get Slow in the First Place
When you merge two tables in Power Query, one of two things happens behind the scenes. Either Power Query translates your merge into a native query that runs efficiently on the source system (this is called query folding), or it gives up on folding and pulls every row from both tables down into memory before doing the join itself, step by step, inside the Power Query engine.
The second scenario is the source of almost every slow merge complaint. It works fine on a small sample, then falls apart the moment you point it at real production data with millions of rows, because Power Query is now doing manually, in memory, what a database would normally do in a fraction of the time using indexes and optimized join algorithms.
Step 1: Check Whether Query Folding Is Still Active
This is the single most important thing to check before trying anything else. Right-click any step in your query and look for "View Native Query." If that option is available (not grayed out), folding is still active at that point. The moment it disappears, something in an earlier step broke folding, and every step after that point runs locally instead of on the source.
Common folding-breakers include custom columns using unsupported functions, certain text transformations, and combining data from more than one source before the merge. Once you find the step where folding breaks, see if you can reorder your steps to push that transformation after the merge instead of before it.
Step 2: Reduce Columns and Rows Before You Merge
Merging all the columns of a wide table, when you only need two or three of them, means Power Query is dragging along a lot of extra weight it doesn't need. Before your merge step, use "Choose Columns" or "Remove Columns" on both tables to strip them down to only what the merge and your final output actually require.
The same logic applies to rows. If you only need the last 12 months of data, filter that early in your query, before the merge, not after. Filtering after the merge means Power Query already did the expensive work of joining data you were going to throw away anyway.
Step 3: Match Data Types on Your Join Keys
If one table's join key is text and the other's is a number, or one is text and the other is text with trailing whitespace, Power Query can still technically merge them, but it often can't fold the operation and may silently produce fewer matches than you expect. Click into both key columns and confirm the data type badge in the column header matches exactly on both sides before you merge.
This one is easy to overlook because the merge doesn't throw an error. It just runs slowly, or returns a smaller result set than it should, without any obvious warning that something's off.
Step 4: Reconsider Your Join Type
| Join type | Relative cost | When to use it |
|---|---|---|
| Inner Join | Lightest | You only need rows that match in both tables |
| Left Outer Join | Light to moderate | You need all rows from the primary table, matched where possible |
| Right Outer Join | Light to moderate | Same as left, but keeping all rows from the secondary table instead |
| Full Outer Join | Heaviest | You genuinely need every row from both tables, matched or not |
Full outer joins are the most resource-intensive option, since Power Query has to account for every possible row from both tables regardless of whether they match. If you're using a full outer join out of habit rather than genuine need, switching to inner or left outer can meaningfully speed things up on large tables.
Step 5: Push the Join to the Source Instead
If both tables live in the same database, the fastest fix is often to skip the Power Query merge entirely. Write a SQL view (or a native query step) that performs the join directly in the database, and import the already-joined result as a single table. Databases are specifically built to handle joins efficiently at scale, in a way that Power Query's in-memory merge engine generally isn't.
let
Source = Sql.Database("your_server", "your_database",
[Query = "SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2025-01-01'"])
in
SourceThis approach also tends to be far faster on tables in the millions-of-rows range, where a Power Query merge would otherwise take minutes. The tradeoff is a bit more SQL knowledge required upfront, and slightly less flexibility if you need to change the join logic frequently through the Power Query UI instead of editing a query string.
Step 6: Use Table.Buffer Selectively
Table.Buffer() loads a table into memory once, which can help when Power Query would otherwise re-evaluate that table multiple times during a merge. It's most useful on the smaller of your two tables, not the larger one.
let
SmallTable = Table.Buffer(Excel.CurrentWorkbook(){[Name="LookupTable"]}[Content]),
Merged = Table.NestedJoin(LargeTable, "KeyColumn", SmallTable, "KeyColumn", "NewColumn", JoinKind.LeftOuter)
in
MergedThis isn't a universal fix. Buffering a very large table can actually make things worse by forcing everything into memory at once instead of letting Power Query stream data efficiently. Test it on your specific case rather than adding it automatically to every merge.
Other Fixes Worth Trying
- Remove duplicates from your smaller table before merging, even if you're confident there aren't any. This can help Power Query recognize a simpler merge pattern.
- Turn off background data preview in Power Query's options, since it fetches data in the background that you may not need while you're still building the query.
- Break a complex query into staging queries instead of one long chain of transformations, so intermediate results can be reused rather than recalculated from scratch each time.
- If you're on Power BI specifically, consider whether DirectQuery mode is a better fit for very large tables, since it queries the source directly instead of importing and merging in memory.
- Check for a hardware bottleneck. If you're working with genuinely large datasets, more RAM or a faster disk can meaningfully help, separate from any query optimization.
Quick Diagnostic Flow
- Right-click your merge step. Is "View Native Query" available? If not, folding has already broken.
- Trace back through your earlier steps to find exactly where folding stopped being available.
- Check both join key columns. Do their data types match exactly?
- Are you pulling more columns or rows than you actually need into the merge?
- Is your join type heavier than necessary? Could inner or left outer replace a full outer join?
- If both tables are in the same database, could the join happen there instead?
If your formulas downstream of a fixed merge still aren't updating the way you expect, that's usually a separate calculation-mode issue rather than a merge performance one. Our guide on why Excel formulas stop updating covers that specifically.
Key Takeaways
- Most slow Power Query merges come down to query folding breaking before the merge step, forcing an in-memory join instead of a fast, source-side one.
- Reducing columns and rows before merging, and matching join key data types, are the two highest-leverage, easiest fixes.
- Full outer joins are the most expensive join type. Use inner or left outer whenever the logic allows it.
- When both tables live in the same database, pushing the join into a SQL view is usually far faster than merging in Power Query.
Table.Buffer()helps in specific cases, mainly on smaller tables re-evaluated multiple times, but isn't a universal fix.
Frequently Asked Questions (FAQs)
Why is my Power Query merge so slow?
The most common cause is query folding breaking somewhere before the merge step, which forces Power Query to pull every row from both tables into memory instead of letting the source database do the join. Mismatched data types on the join keys, unnecessary columns, and full outer joins on large tables are the next most common causes.
What is query folding and why does it matter for merge performance?
Query folding is when Power Query translates your steps into a native query that runs on the source database instead of pulling all the data down first. When folding works, a merge against a database table can run almost as fast as a native SQL join. When it breaks, Power Query has to download and merge everything itself, which is dramatically slower on large tables.
Should I merge in Power Query or push the join to the database instead?
If both tables come from the same database, pushing the join into a SQL view and importing the result as one table is typically far faster than merging in Power Query, since the database engine is built to handle joins efficiently at scale. Merge in Power Query when you're combining data from genuinely different sources, like a database table and an Excel file.
Does Table.Buffer actually speed up a merge?
It can help in specific situations, mainly when the smaller of the two tables is being re-evaluated multiple times during the merge. Buffering loads that table into memory once instead of re-fetching it repeatedly. It is not a universal fix and can sometimes slow things down if used on a very large table, so test it rather than applying it automatically.
Why does my merge work fine with a small dataset but fail on the full dataset?
This usually points to either a memory limit being hit once row counts scale up, or query folding silently breaking under real data conditions that didn't show up in a small sample. Check whether the query is still folding at the merge step, and confirm join keys don't have subtle inconsistencies that only appear in the full dataset, like extra whitespace or mismatched casing.
Related Articles
External References
- Microsoft Fabric Community. "Merge Taking Too Much Time and Refresh Is Failing." community.fabric.microsoft.com
- Microsoft Fabric Community. "PowerQuery: Merge Queries HORRIBLE Performance." community.fabric.microsoft.com
- Spoclearn. "Power Query Best Practices 2026: Faster, Cleaner Models." spoclearn.com
0 Comments