Power Query Problems

Excel Power Query: Sort Six-Row Record Blocks by Column N

Kushani NimanthikaKushani Nimanthika Nov 12, 2026 870 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Create the source table

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.

2
Open Power Query

Select any cell in the Table, open the Data tab, and choose From Table/Range. In Power Query Editor, select Home > Advanced Editor.

3
Replace the query code

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

4
Load and refresh the sorted result

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.

Keep each block intact: The number 6 represents the record height, and the number 2 in Table.Skip([RECORD], 2) selects the third row. The column name N must match the actual header in the source table.
Free Microsoft Office alternative

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. 1. Install WPS Office: Download and open WPS Office, then launch WPS Spreadsheets from the main workspace.
  2. 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. 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.
Open and edit XLSX workbooks with a familiar spreadsheet interface.Work with common Microsoft Excel XLSX and XLS formats for easier migration.Use a lightweight office suite for reviewing and reorganizing report data.Share results in compatible XLSX or CSV formats after completing the block-sorting workflow.
microsoft office alternative - wps office

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.