How to Convert Multiple Rows into One Row in Excel Using Power Query
Question details
The user needs to consolidate downloaded server data where individual records are spread across up to seven rows into a single row per record.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reformatting unorganized downloaded server data for reporting, where blank rows occasionally appear if a record has fewer entries.
- Observed behavior
- Each record currently spans multiple vertical rows instead of being neatly organized horizontally across columns on a single row.
Before attempting to transform your data, ensure your source dataset is formatted as an official Excel Table by pressing Ctrl+T, which makes it easier to load into Power Query and allows seamless updates later.
Use Power Query to Group and Pivot Data
Power Query is the most practical and dynamic method to clean this type of data, as it allows you to group by a record identifier and pivot the attributes into columns without manual copy-pasting.
Power Query can automate the data cleaning process. By setting up the query once, you can reuse it indefinitely whenever new server data is generated.
Select any cell in your source data table, navigate to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.
If your records lack a unique ID, go to 'Add Column' > 'Index Column' or use 'Conditional Column' to assign a group ID to each block of related rows.
Click the dropdown arrow on your data column header and uncheck '(Null)' or '(Blank)' to remove empty rows caused by missing entries in the server export.
Select the column containing your attribute names. Go to the 'Transform' tab and click 'Pivot Column'. In the dialog box, choose the column containing your actual data values. Click 'Advanced options' and select 'Don't Aggregate', then click OK.
Once the data looks correct, go to the 'Home' tab and click 'Close & Load'. This will output the consolidated, single-row records into a new Excel worksheet.
Try WPS Office for Seamless Data Management
Need a lightweight, fast, and highly compatible tool for your daily spreadsheets? WPS Office is a powerful alternative to Microsoft Office that perfectly handles complex data tables without the heavy system requirements.
Frequently Asked Questions
Can I consolidate multiple rows into one using standard Excel formulas instead of Power Query?
Yes, you can use functions like INDEX, MATCH, or the newer FILTER and TEXTJOIN functions to restructure your data. However, this often requires complex formula setups and manual adjustment when the dataset size changes, making Power Query the preferred method.
How do I update the Power Query result when my server data changes?
Simply paste or append the new server data into your original source table. Then, navigate to the consolidated worksheet, right-click anywhere inside the result table, and select 'Refresh'.
Why did Power Query aggregate my data when pivoting the columns?
By default, Power Query tries to count or sum values when pivoting. To prevent this behavior, ensure you click 'Advanced Options' in the Pivot Column dialog box and select 'Don't Aggregate' before confirming.
