Power Query Problems

Reshape One Excel Column into Three Columns with Power Query

Adam DavisAdam Davis Nov 18, 2026 869 views

Question details

Reshape 162 values in one Excel column into 54 rows with three values per row.

Reshape One Excel Column into Groups of Three with Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Each consecutive group of three source values should become one output row.
Observed behavior
The desired result is a three-column table that preserves the original order of the values.
Before you start

Make sure the 162 values are in a single Excel table column with a header. Check the table name under Table Design; the query below uses Table1.

Solution 1Recommended

Split and transpose groups of three in Power Query

Use this query to reshape the source table while preserving the order of consecutive values.

1
Create or select the source table

Select the source column and choose Insert > Table if it is not already a table. On the Table Design tab, set the table name to Table1.

2
Open Power Query

Click a cell in Table1, then choose Data > From Table/Range.

3
Replace the query

In Power Query Editor, select Home > Advanced Editor. Replace the existing code with: let Source = Table.Split(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 3), Transposed = List.Transform(Source, Table.Transpose), Combined = Table.Combine(Transposed) in Combined. Select Done.

4
Load the result

Check that the preview has 54 rows and three columns, then choose Home > Close & Load to place the result in Excel.

Split and transpose groups of three in Power Query
Refresh after changes: If the source values change, use Data > Refresh All to update the query result. A final group with fewer than three values will have nulls in the missing columns.
Reshape data in WPS Spreadsheet

Group a Column into Threes with a WPS Spreadsheet Formula

Use an INDEX formula in WPS Spreadsheet to arrange consecutive values into three columns without Power Query.

  1. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and place the 162 source values in cells A2:A163.
  2. 2. Enter the formula: Click cell C2 and enter =INDEX($A$2:$A$163,(ROW()-ROW($C$2))*3+COLUMN()-COLUMN($C$2)+1).
  3. 3. Fill the output range: Drag the fill handle from C2 across to E2, then down through row 55. The range C2:E55 will contain 54 groups of three.
  4. 4. Save the workbook: Choose File > Save As and select an Excel workbook format such as XLSX.
Arrange each group of three values on one rowOpen and save Microsoft Excel XLSX filesUpdate the output when source cell values changeUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

What if my Excel table is not named Table1?

Select the table and check its name on the Table Design tab. Either rename it Table1 or replace Table1 in the Advanced Editor code with the actual table name.

Will the Power Query result update when I change the source values?

Yes. After editing the source table, choose Data > Refresh All to reload the query result.

What happens if the number of values is not divisible by three?

Power Query keeps the final partial group. Table.Combine fills its missing columns with null values.

Can I group a different number of values per row?

Yes. In Home > Advanced Editor, change the 3 in Table.Split to the desired group size, then select Done and refresh the result.