Forum Discussion
Calculating hours using pivot table
Hi, I'm making a personell planning sheet and I want to calculate the sum of hours teachers give lessons. I have 2 tables and my data is formatted like this (simplified) Lesson name ...
I think you need to make the table like this first. With that dataset it is up to you how you do next steps for your actual needs. Yo
| Lesson | Hours | Teacher |
|---|---|---|
| Lesson 1 | 2 | Paul |
| Lesson 2 | 3 | Pete |
| Lesson 3 | 2 | Paul |
| Lesson 3 | 2 | Pete |
1. Convert the Lesson table to a Table
Select any cell in the Lesson table
Press Ctrl + T
Confirm “My table has headers”
2. Load into Power Query
With the table selected
Go to Data → From Table/Range (Power Query Editor opens)
3. Unpivot the Teacher columns
Hold Ctrl
Select Teacher 1 and Teacher 2
Right-click → Unpivot Columns
4. Clean up
Delete the Attribute column
Rename Value → Teacher
Close and load