Power Query Problems

Fix Excel Power Query Merge Values Changing After Expansion

WPS Content Manager Sep 28, 2026 869 views

Question details

Users experience values changing unexpectedly in Excel Power Query when expanding a merged table.

How to Fix Excel Power Query Merge Values Changing After Expansion

Product: Microsoft Excel
Device & OS: not provided
Scenario: Expanding a merged table in Power Query
Observed behavior: Values appear to change or shuffle after the expansion step due to underlying query evaluation, grouping, sorting, or buffering behavior.

Before you start

Save a local copy of your workbook as a backup before modifying your queries, as Excel for the web has limited support for inspecting complex queries and data connections.

Solution 1Recommended

Buffer the Merge Source with Table.Buffer

Use the Table.Buffer function to load the table into memory, preventing Power Query from re-evaluating the merge step when columns are expanded.

Power Query uses lazy evaluation, meaning it calculates data only when strictly necessary. Expanding a merged table can sometimes force a query to recalculate preceding steps, which alters the row order or values. Buffering the table stops this shifting behavior.

Open the Advanced Editor

In the Power Query Editor, go to the Home tab and click on 'Advanced Editor' to view the M-code behind your query steps.

Locate the Merge Step

Find the line of code where your tables are joined, which typically uses the 'Table.NestedJoin' function.

Apply Table.Buffer

Wrap your Table.NestedJoin function inside Table.Buffer. For example: Table.Buffer(Table.NestedJoin(#"Previous Step", {"KeyColumn"}, OtherTable, {"KeyColumn"}, "NewColumnName", JoinKind.LeftOuter)).

Expand the Merged Table

Click 'Done' in the Advanced Editor. You can now safely click the expand icon on the merged column in the query editor; the previous columns will remain unchanged.

Buffer the Merge Source with Table.Buffer

Buffering Performance Impact: While Table.Buffer ensures stability by loading the data into memory, it can increase memory usage and slow down refresh times on exceptionally large datasets.

Solution 2

Sort Grouped Tables Before Adding Index Columns

Sort your data properly inside grouped tables before adding an index column to guarantee the data remains static upon expansion.

Free Microsoft Office alternative

Experience Seamless Data Management with WPS Office

While highly complex Power Query scripts requiring advanced M-code modifications are specific to Microsoft Excel, WPS Office provides a lightweight, free alternative for comprehensive everyday data analysis. Enjoy high compatibility with Excel files, a familiar spreadsheet interface, and built-in tools that make data processing straightforward without the need for complex query troubleshooting.

1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
2. Open WPS Spreadsheet: Launch the application and open the WPS Spreadsheet module, which is fully equipped for data analysis.
3. Import Your Data: Open your existing .xlsx or .csv files to seamlessly manage, filter, and analyze your datasets without complex query configurations.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats without data loss.Lightweight architecture for faster loading and processing of large datasets.Familiar spreadsheet interface that eliminates the learning curve.Free alternative featuring robust data management tools and pivot tables.

microsoft office alternative - wps office

Frequently Asked Questions

Why do Power Query values change when I expand a merged table?

This happens because of Power Query's lazy evaluation engine. Expanding a table can trigger a re-evaluation of upstream sorting, grouping, or data connections. If the order isn't explicitly locked into memory, the values may shift.

What does Table.Buffer do in Excel Power Query?

Table.Buffer loads the specified table entirely into memory during query execution. This effectively isolates previous query steps, preventing Power Query from re-evaluating them when downstream actions—like column expansion—occur.

Can I troubleshoot Power Query issues in Excel for the web?

Excel for the web has highly limited support for inspecting queries, connections, and Advanced M-code. It is recommended to download a local copy of your workbook and use the desktop version of Excel to edit and troubleshoot complex queries.

Excel & Data Problems