Power QueryExercises

HR roster normalization

Pemberton Freight (fictional)

Rebuild a clean staff table from a printed roster report with title rows, department blocks and subtotals, apply the leavers list and report headcount by department.

Level
intermediate
Time
~55 min
Deliverables
2
Rubric
v1

Access

Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.

Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).

Business goal

Give HR a clean staff table as of 31 March 2026 and a headcount by department that stays correct when the roster report's layout shifts.

Pemberton Freight's HR system can only print its roster as a report: a title block, then one block per department with a department line, the people in it and a subtotal. The roster also lags behind reality: people who have handed in their notice stay Active until payroll closes them, so HR keeps a separate leavers list. The HR lead wants one row per person with their real status as of 31 March 2026, and a headcount per department.

Graded deliverables

  1. 1. Staff

    65 points

    One clean row per person in the roster with department, cost centre and real status.

    • Columns: EmpID, FirstName, LastName, Department, CostCentre, StartDate, FTE, Status
    • One row per person; no title, department, subtotal, empty or footer rows
    • Status is Active, On leave or Left as of 31 March 2026
    • Departments without a cost centre show UNMAPPED
    Expected output schema for Staff
    ColumnType
    EmpIDtext
    FirstNametext
    LastNametext
    Departmenttext
    CostCentretext
    StartDatedate
    FTEdecimal
    Statustext
    Grain:
    One row per person listed in the roster report.
    Order:
    Not graded.
    Nulls:
    No nulls. Unknown cost centres are "UNMAPPED".
  2. 2. Department Headcount

    35 points

    Current headcount and FTE per department, built on your Staff query.

    Builds on: Staff

    • Columns: Department, CostCentre, Headcount, FTE, OnLeave
    • Leavers excluded; people on leave counted
    • Sorted by Department
    Expected output schema for Department Headcount
    ColumnType
    Departmenttext
    CostCentretext
    Headcountwhole number
    FTEdecimal
    OnLeavewhole number
    Grain:
    One row per department with at least one current employee.
    Order:
    Graded: Department ascending.
    Nulls:
    No nulls.
Checking your access…