Power Query Problems

How to Preserve Worksheet Order After Merging in Power Query

Olivia Miller Oct 9, 2026 872 views

Question details

The user wants to maintain the original sequence of dynamically generated worksheet names after merging data using Power Query.

Preserve Worksheet Order After Merging Power Query Queries

Product: Excel / Power Query
Device & OS: not provided
Scenario: Merging data queries from another workbook where the source order needs to be preserved in the final output.
Observed behavior: Power Query operations alter the original row sequence, causing the dynamically generated worksheet names to lose their original order after merging and expanding.

Before you start

Ensure you have fully loaded your initial source queries into the Power Query Editor before attempting the merge, and save a backup of your workbook.

Solution 1Recommended

Add an Index Column Before the Merge Operation

By generating an Index column before merging, you create a permanent reference to the original row sequence that you can sort by later.

Power Query operations, similar to relational database joins, process data for efficiency rather than preserving row order. Because of this, expanding merged tables often scrambles the initial sequence. Adding an Index column ensures you always have a numerical roadmap back to your original layout.

Open the Power Query Editor

Navigate to the 'Data' tab in your Excel ribbon and select 'Get Data' > 'Launch Power Query Editor' to view your loaded queries.

Insert an Index Column

Select the query whose order you want to preserve. Go to the 'Add Column' tab on the top ribbon, click 'Index Column', and choose 'From 1'. This assigns a sequential number to every row.

Perform the Query Merge

Switch to the 'Home' tab, click 'Merge Queries', and select the secondary query from the other workbook. Match the corresponding columns and click 'OK'.

Sort by the Index Column

After expanding the merged data, locate your new Index column. Click the drop-down arrow on its header and select 'Sort Ascending' to instantly restore your original worksheet order.

Add an Index Column Before the Merge Operation

Order Successfully Preserved: Sorting by the Index column prevents Power Query from defaulting to alphabetical sorting, keeping your custom worksheet order entirely intact.

Free Microsoft Office alternative

Need a Powerful and Lightweight Spreadsheet Tool? Try WPS Office

While advanced Power Query merges in Microsoft Excel can sometimes unexpectedly disrupt your data's original structure, WPS Office provides a highly compatible and intuitive alternative. It is perfect for users who need to organize, merge, and analyze worksheets without dealing with overly complex background data models.

1. Download the Software: Visit the official WPS Office website to download the free suite for your operating system.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx workbooks with zero formatting loss.
3. Organize Your Data Easily: Utilize the built-in Data tab tools to sort, filter, and consolidate your information safely and efficiently.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Intuitive and user-friendly interface that feels familiar right away.Lightweight installation that runs smoothly on older and newer devices alike.Completely free to download and use for all essential data consolidation tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query change the row order during a merge?

Power Query processes data in the most efficient manner possible to handle large volumes of information. Like SQL databases, it does not inherently guarantee the preservation of original row sequences after joins or merges unless you explicitly apply an Index or Sort command.

Can I just sort by the dynamically generated worksheet names instead?

You can only sort by worksheet names if your desired original order happens to be strictly alphabetical or chronological. If your original sequence was custom, an alphabetical sort will scramble the order, making the Index column method the only reliable solution.

How do I remove the Index column after the final sorting is done?

Once you have completed the merge and sorted your table by the Index column, you can safely right-click the Index column header and select 'Remove'. The sorting step will still be preserved in the 'Applied Steps' pane on the right side of the Power Query Editor.

Excel & Data Problems