How to Combine Repeated Columns into Rows with Excel Power Query

Select any cell inside your data table, navigate to the Data tab on the Excel ribbon, and click 'From Table/Range'. This action opens the Power Query Editor window.

In the Power Query Editor, hold down the Ctrl key and click the headers of all the repeated columns (e.g., Child 1 Name, Child 2 Name, Child 1 Age, Child 2 Age) that you want to flatten into rows.

Right-click on one of the highlighted column headers and select 'Unpivot Columns' from the drop-down menu. Your data will instantly reformat, shifting the selected column headers into an 'Attribute' column and the row data into a 'Value' column.

Double-click the new 'Attribute' and 'Value' headers to rename them appropriately (e.g., 'Field' and 'Data'). If needed, use the 'Split Column' feature on the Home tab to separate number identifiers from text in the Attribute column.

Once your data is correctly shaped, click the 'Close & Load' button located on the Home tab. This will export your freshly transformed, row-based data into a new worksheet.

Open your table in the Power Query Editor. Instead of selecting the repeated columns, select only the columns that uniquely identify the respondent, such as 'Respondent ID' or 'Timestamp'.

Right-click the header of your selected static column(s) and choose 'Unpivot Other Columns'. Power Query will instantly gather all unselected columns and flatten them into rows.

Format the resulting data as needed, then click 'Close & Load' to return the data to your Excel workbook. Future columns added to the source table will now be unpivoted automatically upon refreshing.