Consolidate Student Records into One Row in Excel Power Query
Question details
Combine student information spread across multiple rows and columns into one complete row per student.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Thousands of student records need to be consolidated into a structured table that can be refreshed.
- Observed behavior
- The desired output has one row per student, with each name and other detail in its own identified column.
Identify a unique student ID and determine whether each name belongs to the student, a parent, or another person. Make sure each source row can be associated with the correct student and that fields have consistent labels before grouping.
Group student records with Excel Power Query
Use this method to build a refreshable table with one row per student.
The grouping method below assumes each source row has a student ID and that each type of detail is in a consistent column. If names or labels are mixed together, identify and separate them first; do not assume that an unlabeled name belongs to the student.
The example returns the first nonblank value in each field. Review students who have multiple different values for the same field before relying on the output.
Select the source data and choose Insert > Table. Confirm that the table has headers, including a student ID and a separate column for each identified field.
Select a cell in the table, then choose Data > From Table/Range. In Excel 2016, Power Query commands are in the Get & Transform section of the Data tab.
In Power Query Editor, correct column headers and separate or rearrange fields as needed. If an ID is blank only because it continues from the preceding row, select the ID column and choose Transform > Fill > Down; verify that this does not cross student boundaries.
Select the student ID column, choose Home > Group By, and select the All Rows operation. Name the grouped column Rows.
Choose Add Column > Custom Column. For a source column named Student Name, enter List.First(List.Select([Rows][Student Name], each _ <> null and Text.Trim(Text.From(_)) <> ""), null). Repeat for each required source column, changing Student Name to the relevant column name.
Remove the Rows column after extracting the required fields, then choose Home > Close & Load. When source data changes, right-click the output table in Excel and select Refresh.

Create one row per student in WPS Spreadsheet
For a consistently structured source table, use student IDs and lookup formulas in WPS Spreadsheet to collect the last nonblank value from each field into one row per student.
- 1. Organize the source: Open the workbook in WPS Spreadsheet. On a sheet named Source, put a student ID in column A for every source row and place each identified detail in its own consistently labeled column.
- 2. Create a unique ID list: Copy the IDs from Source column A to column A of a new sheet. Select the copied IDs and use Data > Remove Duplicates to keep one entry per student.
- 3. Retrieve the first detail: If the first detail is in Source column B and the first ID is in A2, enter =IFERROR(LOOKUP(2,1/((Source!$A$2:$A$10000=$A2)*(Source!B$2:B$10000<>"")),Source!B$2:B$10000),"") in B2. Adjust the source row limits to include your data.
- 4. Fill and review: Drag the fill handle across the detail columns and down the student list. Check the results: the formula returns the last nonblank value for each ID and field, so investigate any conflicting values in the source.

Frequently Asked Questions
How do I identify which student a name belongs to?
Use the source labels and a reliable student ID to determine whether the name is the student's, a parent's, or someone else's. Resolve unlabeled or ambiguous names before grouping; Power Query cannot infer their relationships reliably.
What if the student ID appears only on the first row of each record?
In Power Query Editor, select the ID column and choose Transform > Fill > Down, but only if the blank rows truly belong to the preceding student. Check record boundaries before applying it.
How do I update the consolidated table after adding records?
Add the records to the original Excel source table, then right-click the query output table and select Refresh. New records must follow the same layout and labels used by the query.
What happens if a student has two different names in the same field?
The Power Query example selects the first nonblank value, while the WPS formula selects the last. Neither resolves conflicts automatically, so review and correct conflicting source values.




