Excel Power Query: Sort Six-Row Record Blocks by Column N
Question details
The user needs to sort database report records arranged in six-row blocks without separating the rows belonging to each record.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Each record occupies six consecutive rows, and the sort key is stored in row 3 of each block, column N.
- Observed behavior
- A normal row sort would separate the six rows within each record, so the complete blocks must be sorted as single units.
Place the source range in an Excel table named Table and confirm that the total row count is divisible by six. Check that column N contains a usable sort value in the third row of every six-row block.
Use Power Query to sort complete six-row records
Use this reliable approach when the report is refreshed regularly or contains many six-row records.
Power Query groups the source table into six-row nested tables, reads the value from the third row and column N of each group, sorts the groups by that key, and combines them back into one table. The rows inside each record remain together.
Select the report range in Excel, choose Insert > Table, confirm that the table has headers if applicable, and set its table name to Table on the Table Design tab.
Select any cell in the Table, open the Data tab, and choose From Table/Range. In Power Query Editor, select Home > Advanced Editor.
Replace the existing code with the following M code, then select Done: let Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content], TableSplit = Table.Split(Source, 6), ToTable = Table.FromList(TableSplit, Splitter.SplitByNothing(), {"RECORD"}, null, ExtraValues.Error), SortBy = Table.AddColumn(ToTable, "SortBy", each Table.First(Table.Skip([RECORD], 2))[N]), SortedRows = Table.Sort(SortBy, {{"SortBy", Order.Ascending}}), RemovedSortBy = Table.RemoveColumns(SortedRows, {"SortBy"}), CombinedRecords = Table.Combine(RemovedSortBy[RECORD]) in CombinedRecords
Choose Home > Close & Load to place the sorted records in a worksheet. When the source changes, use Data > Refresh All to rebuild the sorted six-row blocks.
Sort six-row blocks with an Excel 365 formula
Use this option when Microsoft 365 dynamic-array functions are available and you want a formula-driven result.
Move Six-Row Excel Reports to a Lightweight WPS Office Workflow
WPS Office is a free, lightweight alternative for opening, reviewing, editing, and sharing Excel workbooks. It preserves familiar spreadsheet workflows and supports common Microsoft Excel formats, making migration straightforward, although Excel Power Query-specific queries may need to be recreated with available spreadsheet tools.
- 1. Install WPS Office: Download and open WPS Office, then launch WPS Spreadsheets from the main workspace.
- 2. Open the Excel report: Choose Menu > Open, select the XLSX or XLS report, and verify that the six-row records and column N values appear correctly.
- 3. Prepare a sorted working copy: Use WPS Spreadsheets to inspect the block keys and organize the report while keeping each six-row record together; save the result as an XLSX file through Menu > Save As.

Frequently Asked Questions
Why can’t I sort the worksheet normally by column N?
A standard worksheet sort treats each row independently, so it can separate the six rows that form one record. Sort the records as grouped six-row blocks with Power Query or a block-aware formula instead.
Which Power Query value identifies each six-row record?
The query uses Table.First(Table.Skip([RECORD], 2))[N]. Table.Skip removes the first two rows of the block, and Table.First selects the third row before reading column N.
What should I change if each record has a different number of rows?
Replace every occurrence of 6 with the actual block height. The source row count must be an exact multiple of that height unless you add separate handling for an incomplete final block.
Can I use the formula method without Microsoft 365?
Usually not, because the formula relies on dynamic-array functions including LET, SEQUENCE, SORTBY, TOCOL, and CHOOSEROWS. Use Power Query in supported Excel versions or another grouping workflow instead.
How do I update the sorted output after adding records?
For Power Query, add the records to the source table and select Data > Refresh All. For the formula method, make sure the data reference includes the new rows and that the formula spill area is clear.




