Power Query Problems

Create an Employee and Project Summary with Excel Power Query and PivotTables

John WilsonJohn Wilson Nov 18, 2026 870 views

Question details

Create a maintainable employee and project summary from assignment data arranged across multiple columns.

Create an Employee and Project Summary with Excel Power Query and PivotTables
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.
Before you start

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.

Solution 1Recommended

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.

1
Load the source table

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.

2
Unpivot repeated columns

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.

3
Shape the assignment fields

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.

4
Load the normalized data

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.

5
Create the summary

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.

6
Add slicers and refresh

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.

Normalize assignments in Power Query and build a PivotTable
Keep related values together: If employee, priority, and role values are stored in separate repeated column groups, preserve an assignment identifier while reshaping them so the values remain matched to the correct project.
Free Microsoft Office alternative

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.

Review and edit employee and project assignment tablesCompatible with Microsoft Excel XLSX and XLS spreadsheet formatsFamiliar spreadsheet interface for an easier transitionFree and lightweight office suite
microsoft office alternative - wps office

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.