How to Transpose Variable-Length Records into Columns in Power Query
Question details
The user needs to transpose records of varying lengths into columns without values from different groups getting improperly combined.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transforming and structuring irregular data where each category or name has a different number of associated rows.
- Observed behavior
- Power Query incorrectly combines values from different names when records are transposed directly, mixing up data between groups with varying row counts.
Ensure your dataset is sorted by the primary grouping column (e.g., Name) and make a backup of your original query before editing formulas in the Advanced Editor.
Group Data by Name and Extract as New Columns
By grouping the records first, you create a nested table that keeps variable-length data aligned with its correct category before expanding it into columns.
Transposing data directly can cause misalignment if the number of rows per group varies. The safest method is to consolidate the variables you need, group them by the main identifier, and then extract the lists into separate columns.
Select the columns you want to consolidate (e.g., Weight and Time), right-click the column headers, and choose 'Merge Columns'. Provide a name like 'Merged'.
Select your primary identifier column (e.g., Name). Navigate to the Transform tab and click 'Group By'. In the dialog, set the Operation to 'All Rows' and name the new column 'Group'.
Go to the Add Column tab and click 'Custom Column'. Enter the M formula = Table.Column([Group], "Merged") to pull the merged values into a list for each name.
Click the extract icon on the header of your new custom column and choose to extract the values. Once extracted, use the 'Split Column' feature to separate the values into individual columns.
Select the original 'Group' column (and any other intermediate columns you no longer need), right-click, and select 'Remove' to finalize your table structure.
Try WPS Office for Simplified Data Management
While Power Query offers advanced M formulas for complex transformations, WPS Spreadsheet provides an intuitive, lightweight, and highly compatible alternative for everyday data processing. Enjoy seamless compatibility with Microsoft Excel formats without the heavy subscription costs.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Data: Launch WPS Spreadsheet and easily open your existing .xlsx or .csv files with zero format loss.
- 3. Transform Data: Use intuitive tools like Paste Special > Transpose, or powerful built-in PivotTables to reorganize your data effortlessly.
Frequently Asked Questions
Why does Power Query mix up rows when transposing?
When transposing data directly, Power Query converts rows to columns purely based on their index sequence. If the data isn't perfectly symmetrical or explicitly grouped first, values from different categories will shift and misalign.
What does 'Group By -> All Rows' do in Power Query?
Selecting 'All Rows' during a Group By operation takes all the original records associated with that specific group and packs them into a single nested 'Table' cell. This preserves the exact records for each category so you can process them independently later.
Can I transpose variable-length data without writing M code in the Advanced Editor?
Yes, many transformation scenarios can be handled entirely through the UI. By creatively using 'Merge Columns', 'Group By', and 'Split Column', you can achieve the transposed result. However, adding a Custom Column requires typing a very short formula like = Table.Column([ColumnName], "Target").
