Create an Employee and Project Summary with Excel Power Query and PivotTables
Question details
Create a maintainable employee and project summary from assignment data arranged across multiple columns.

- Product
- Microsoft Excel Power Query and PivotTables
- Device & OS
- not provided
- Scenario
- The user wants to summarize project assignments, employee names, priorities, and roles and filter the results.
- Observed behavior
- The desired result is a normalized assignment table and a refreshable PivotTable with slicers.
Check which source columns identify each assignment and which contain repeated employee or project fields. Ensure the source has clear headers and that employee, project, priority, and role values can be associated with the correct assignment.
Normalize assignments in Power Query and build a PivotTable
Use this method when assignments are spread across columns and you need a summary that can be refreshed as the source changes.
Aim for one row per assignment, with separate Employee, Project, Priority, and Role columns. The exact transformations depend on how the original headers and values are arranged; unpivoting alone may not create all four fields.
Select a cell in the source data, choose Insert > Table, and confirm that the table has headers. Then select Data > From Table/Range to open it in Power Query.
In Power Query Editor, select the columns that identify each record, then choose Transform > Unpivot Columns > Unpivot Other Columns. The remaining column headers and values appear in Attribute and Value columns.
Use the Attribute and Value columns to identify the employee, project, priority, and role for each assignment. If headers encode multiple pieces of information, select Attribute and use Transform > Split Column; if different field types appear in rows, use Transform > Pivot Column where appropriate. Rename the resulting columns and filter out blank assignments.
Select Home > Close & Load To, choose Table, and load the results to a worksheet. Check that each row represents an assignment and that the four fields contain the expected values.
Select a cell in the loaded table and choose Insert > PivotTable. Choose the Summary sheet as the destination, then drag Employee, Project, Priority, and Role into the Rows, Columns, or Filters areas as needed. Drag a populated field such as Project into Values and set it to Count to count assignment rows.
Select the PivotTable, choose PivotTable Analyze > Insert Slicer, and select Employee, Project, and Priority. After changing the source table, choose Data > Refresh All to update the query and PivotTable.

Work with employee and project spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open compatible Excel files and continue working with your data as you move between office suites; it does not replace Excel's Power Query workflow.

Frequently Asked Questions
Why should I unpivot the assignment data?
Unpivoting converts repeated column headers into row values, making it easier to organize assignments into consistent fields for a PivotTable. In Power Query Editor, select the identifier columns and choose Transform > Unpivot Columns > Unpivot Other Columns.
Will unpivoting automatically create Employee, Project, Priority, and Role columns?
Not necessarily. Depending on the source layout, you may need to split the Attribute column, pivot field types back into columns, or use assignment identifiers to keep related values together.
How do I filter the summary by employee or project?
Select the PivotTable, choose PivotTable Analyze > Insert Slicer, and select Employee, Project, or Priority. Use the resulting slicer buttons to filter the summary.
How do I update the summary after adding assignments?
Add the new assignments within the source Excel table, then choose Data > Refresh All. This refreshes the query and the PivotTable based on its results.




