Why Excel Power Query Does Not Preserve Row Order in the Data Model
Question details
The user wants to understand why a sorted Power Query result does not retain its row order after loading and how to display the records in the required sequence.
- Product
- Microsoft Excel Power Query and the Excel Data Model
- Device & OS
- not provided
- Scenario
- A query contains 10,000 rows and 16 columns, with a final step that sorts the Index column in ascending order.
- Observed behavior
- The loaded result does not appear in Index order, although sorting appears to work when only five columns remain.
Check whether the query loads to a worksheet table or to the Data Model; the Data Model does not guarantee physical row order. Confirm that the Index field is numeric, uniquely identifies each record, and represents the sequence you want to display.
Display Data Model records in order using a flattened PivotTable
Use this approach when the query loads to the Excel Data Model and you need records displayed in a specific sequence.
Sorting in Power Query defines the order at that query step, but it does not establish a permanent row order inside the Data Model. Apply ordering in the report that displays the data instead.
An Index field supplies an explicit ordering key. Placing it first in a flattened PivotTable lets you display individual records in sequence without relying on the model's internal storage order.
In Excel, open Data > Queries & Connections and double-click the query. In Power Query Editor, select the Index column and set its data type to Whole Number. If no index exists, establish the intended order first, then select Add Column > Index Column > From 1.
In Power Query Editor, select Home > Close & Load > Close & Load To. Choose Only Create Connection, select Add this data to the Data Model, and click OK. For an existing query, you can also right-click it in Queries & Connections and select Load To.
Select Insert > PivotTable > From Data Model where available. In versions with a different dialog, select Use this workbook's Data Model. Choose a worksheet location and create the PivotTable.
In the PivotTable Fields pane, drag Index to the top of the Rows area. Drag the other columns you want to display into Rows beneath it, in the desired column order. A unique Index keeps individual records separate, even when their other field values match.
Click inside the PivotTable, then select Design > Report Layout > Show in Tabular Form and Repeat All Item Labels. Select Design > Subtotals > Do Not Show Subtotals and Design > Grand Totals > Off for Rows and Columns.
Right-click a numeric item in the PivotTable's Index column and select Sort > Sort Smallest to Largest. Check the resulting sequence after refreshing the query and PivotTable.
Use WPS Spreadsheet for everyday table sorting
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making migration straightforward for ordinary worksheet tasks. Use WPS Spreadsheet to sort a flat table by an Index column; it does not fix Excel Power Query or replace the Data Model workflow described here.
Frequently Asked Questions
Why sort the Index column if its values are already sequential?
The Index values encode the intended sequence, but the Data Model does not guarantee that records will be displayed in that sequence. In the PivotTable, right-click an Index item and select Sort > Sort Smallest to Largest to explicitly order the output.
Does the final Power Query sort step guarantee Data Model row order?
No. A final sort step orders the query result at that stage, but the Data Model does not preserve a guaranteed physical row sequence. Include the ordering field in the PivotTable and apply the sort there.
Why does the result look sorted with five columns but not with 16?
The reported behavior alone does not establish the exact cause. A change in the apparent order is not evidence that the Data Model guarantees sorting for smaller tables. Use an explicit Index sort in the display regardless of the number of columns.
How do I stop the PivotTable from combining identical records?
Use a unique Index for each record and place Index first in the PivotTable's Rows area. Add the remaining record fields beneath it, then select Design > Report Layout > Show in Tabular Form to display them as separate columns.
Can WPS Spreadsheet preserve this Excel Power Query workflow?
WPS Spreadsheet should not be treated as a replacement for Excel Power Query and Data Model features. For ordinary sorting, export or copy the query output into a regular worksheet table, open it in WPS Spreadsheet, select the full table, and use Data > Sort to sort the Index column in ascending order.
