Unpivot Assessment Data in Excel Power Query for a PivotTable
Question details
The user needs to convert multiple course assessment columns into a single assessment-type field so the data can be counted correctly in a PivotTable.
- Product
- Microsoft Excel Power Query and PivotTable
- Device & OS
- not provided
- Scenario
- Preparing course assessment data for analysis by academic level, subject group, and assessment type.
- Observed behavior
- Assessment types are stored in separate columns, which prevents a PivotTable from treating them as one consistent category.
Make sure the source range is formatted as an Excel table and includes clear headers for academic level, subject group, and each assessment column. Identify the columns that should remain as descriptive fields before unpivoting the assessment columns.
Unpivot assessment columns in Excel Power Query and create a PivotTable
Use this method when assessment types are stored in separate columns and need to become one category field for accurate PivotTable counts.
Unpivoting converts several assessment columns, such as Exam, Coursework, and Presentation, into two fields: an assessment-type field and a value field. The remaining columns, including academic level and subject group, stay attached to each resulting record.
Click any cell in the source table, then select Data > From Table/Range. Confirm that the table has headers when Excel asks to create or open the query.
In the Power Query Editor, select the assessment columns you want to combine into one list, such as Exam, Coursework, and Presentation. Do not select identifier columns such as Academic Level or Subject Group.
Open Transform > Unpivot Columns. Power Query creates an Attribute column containing the assessment type and a Value column containing the original values.
Double-click the Attribute header and rename it to Assessment Type. Rename Value to Assessment Value, or another name that matches the meaning of the data.
Select Home > Close & Load, then choose to load the transformed table. Click in the loaded table and select Insert > PivotTable. Place Academic Level and Subject Group in Rows, Assessment Type in Columns or Rows, and Assessment Type or Assessment Value in Values using Count as the summary calculation.
Create a Normalized Assessment PivotTable in WPS Spreadsheet
WPS Spreadsheet can help you organize assessment records into a row-based table and create a PivotTable for counts by academic level, subject group, and assessment type. Arrange each assessment as one record before inserting the PivotTable.
- 1. Open the assessment workbook: Open the Excel assessment file in WPS Spreadsheet and confirm that the first row contains column headers.
- 2. Arrange assessment data in rows: Create or use columns such as Academic Level, Subject Group, Assessment Type, and Assessment Value, with one assessment type recorded on each row.
- 3. Select the normalized data range: Click a cell in the completed row-based data table, including its header row.
- 4. Insert a PivotTable: Select Insert > PivotTable, confirm the source range, and choose where to place the PivotTable.
- 5. Set up the PivotTable fields: Drag Academic Level and Subject Group to Rows, drag Assessment Type to Columns or Rows, and add Assessment Type or Assessment Value to Values. Set the value calculation to Count if needed.
Frequently Asked Questions
Which columns should I unpivot in an assessment data table?
Select only the columns that represent separate assessment types, such as Exam, Quiz, Coursework, or Presentation. Keep fields such as Academic Level, Subject Group, Student ID, and Course as regular columns.
What do the Attribute and Value columns mean after unpivoting?
The Attribute column contains the original assessment column names and should usually be renamed Assessment Type. The Value column contains the corresponding cell values, such as scores, grades, or completion indicators.
How do I count each assessment type in the PivotTable?
In the PivotTable Fields pane, place Assessment Type in Rows or Columns and drag Assessment Type or Assessment Value into Values. Open Value Field Settings and select Count if Excel does not choose it automatically.
Can I refresh the PivotTable after adding more assessment data?
Yes. Add the new records to the original Excel table, then select Data > Refresh All to refresh the Power Query output. Right-click the PivotTable and select Refresh if it does not update automatically.
