Build a monthly HR headcount report with Power Query
Use case · By Tahir Nas · Examples run in our simulator · Updated
12 min read
A monthly headcount report counts, for each month end, the employees whose start date is on or before that day and who have not yet left. In Power Query you build a list of month ends, then count the employee rows that pass that test for each one; department splits, FTE and starters and leavers all come from the same rule.
The data you need
Most HR systems can export an employee list with one row per employment: an ID, a department, a start date, a leave date (blank for current staff) and, if people work part time, a full-time equivalent (FTE) value. That's enough for a headcount report. You don't need monthly snapshots from the HR system, because the dates already say who was employed when.
Keep the export to the columns the report needs. Names, salaries and personal details add nothing to a headcount figure, and leaving them out of the query means they can't end up in a shared workbook by accident. The ID is enough to trace a row back to the HR system.
Two details are worth checking before you build anything:
- Leave dates are real dates. Exports often mix true dates, text such as "31/01/2026" and blanks. Convert them with the right locale (the lesson on converting dates and numbers with a locale covers this) and make blanks
null, not empty text. - What the leave date means. It may be the last day worked or the first day after employment ends. Check in your HR system; the rest of this post treats it as the last day of employment.
Count active employees at each month end
The core of the report is a table of month ends with a count next to each. Date.EndOfMonth returns the last day of the month that contains a date, and Date.AddMonths steps forward a month at a time, so four month ends from December 2025 take one line:
let
Employees = #table(
type table [ID = text, Department = text, Start = date, Leave = nullable date, FTE = number],
{
{"E01", "Sales", #date(2024, 3, 1), null, 1},
{"E02", "Sales", #date(2025, 11, 17), #date(2026, 2, 13), 1},
{"E03", "Finance", #date(2023, 6, 5), null, 0.6},
{"E04", "Finance", #date(2026, 1, 12), null, 1},
{"E05", "Warehouse", #date(2022, 9, 1), #date(2026, 1, 31), 1},
{"E06", "Warehouse", #date(2026, 2, 2), null, 0.5},
{"E07", "Sales", #date(2026, 3, 9), null, 1}
}
),
MonthEnds = List.Transform({0..3}, each Date.EndOfMonth(Date.AddMonths(#date(2025, 12, 1), _))),
Months = Table.FromList(MonthEnds, Splitter.SplitByNothing(), type table [Month End = date]),
Headcount = Table.AddColumn(Months, "Headcount", (m) =>
Table.RowCount(Table.SelectRows(Employees, each [Start] <= m[Month End] and ([Leave] = null or [Leave] > m[Month End]))), Int64.Type)
in
HeadcountResult
| Month End | Headcount |
|---|---|
| 2025-12-31 | 4 |
| 2026-01-31 | 4 |
| 2026-02-28 | 4 |
| 2026-03-31 | 5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result is 4, 4, 4 and 5 for the ends of December, January, February and March. January stays at 4 because E04 joined and E05 left; February stays at 4 because E06 joined and E02 left; March rises to 5 when E07 joins.
The custom column uses (m) => rather than each because there are two row contexts in play: the month-end row m, and each employee row inside Table.SelectRows. Naming the outer one keeps [Start] and [Leave] pointing at the employee. The lesson on row context and referencing other rows explains why that matters.
The month-end rule is a decision
E05's last day is 31 January. Is E05 in the January headcount? The query says no, because the test is [Leave] > m[Month End]: closing headcount counts the people still employed going into the next month. Changing > to >= counts E05 in January instead, and both are defensible. What isn't defensible is using one rule here and the other in the starters and leavers count, which is how headcount reports end up failing to reconcile. Agree the rule with HR, write it next to the report, and use it everywhere.
Headcount and FTE by department
A department split needs the active employees for each month, not just their number. Keep the matching rows as a nested table, expand the columns you need, and group by month and department:
let
Employees = #table(
type table [ID = text, Department = text, Start = date, Leave = nullable date, FTE = number],
{
{"E01", "Sales", #date(2024, 3, 1), null, 1},
{"E02", "Sales", #date(2025, 11, 17), #date(2026, 2, 13), 1},
{"E03", "Finance", #date(2023, 6, 5), null, 0.6},
{"E04", "Finance", #date(2026, 1, 12), null, 1},
{"E05", "Warehouse", #date(2022, 9, 1), #date(2026, 1, 31), 1},
{"E06", "Warehouse", #date(2026, 2, 2), null, 0.5},
{"E07", "Sales", #date(2026, 3, 9), null, 1}
}
),
MonthEnds = List.Transform({0..3}, each Date.EndOfMonth(Date.AddMonths(#date(2025, 12, 1), _))),
Months = Table.FromList(MonthEnds, Splitter.SplitByNothing(), type table [Month End = date]),
WithEmp = Table.AddColumn(Months, "Rows", (m) => Table.SelectRows(Employees, each [Start] <= m[Month End] and ([Leave] = null or [Leave] > m[Month End]))),
Expanded = Table.ExpandTableColumn(WithEmp, "Rows", {"Department", "FTE"}),
Grouped = Table.Group(Expanded, {"Month End", "Department"}, {{"Headcount", each Table.RowCount(_), Int64.Type}, {"FTE", each List.Sum([FTE]), type number}}),
Sorted = Table.Sort(Grouped, {{"Month End", Order.Ascending}, {"Department", Order.Ascending}})
in
SortedResult
| Month End | Department | Headcount | FTE |
|---|---|---|---|
| 2025-12-31 | Finance | 1 | 0.6 |
| 2025-12-31 | Sales | 2 | 2 |
| 2025-12-31 | Warehouse | 1 | 1 |
| 2026-01-31 | Finance | 2 | 1.6 |
| 2026-01-31 | Sales | 2 | 2 |
| 2026-02-28 | Finance | 2 | 1.6 |
| 2026-02-28 | Sales | 1 | 1 |
| 2026-02-28 | Warehouse | 1 | 0.5 |
| 2026-03-31 | Finance | 2 | 1.6 |
| 2026-03-31 | Sales | 2 | 2 |
| 2026-03-31 | Warehouse | 1 | 0.5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The output has 11 rows. Finance shows 1 person and 0.6 FTE in December, then 2 people and 1.6 FTE from January. Headcount and FTE answer different questions: headcount is people, FTE is capacity. A department with two part-timers can have the same FTE as one with a single full-timer.
Look at January: there is no Warehouse row. E05 left on 31 January and E06 didn't start until February, so the warehouse had nobody at month end, and a group can't produce a row for a combination with no members. If the report needs a zero there (a pivot table with a fixed layout, say), build the grid of every month and every department first and merge the counts onto it. The stock on hand post builds the same kind of grid for items and month ends. The exercise Fill in missing store and month combinations with zero practises exactly that step.
Starters, leavers and a reconciliation check
A headcount report is more convincing when the movement between months is visible. For each month: opening headcount (active the day before the month starts), starters (start date in the month), leavers (leave date in the month) and closing headcount. Write the active test once as a function so opening and closing use the same rule:
let
Employees = #table(
type table [ID = text, Department = text, Start = date, Leave = nullable date, FTE = number],
{
{"E01", "Sales", #date(2024, 3, 1), null, 1},
{"E02", "Sales", #date(2025, 11, 17), #date(2026, 2, 13), 1},
{"E03", "Finance", #date(2023, 6, 5), null, 0.6},
{"E04", "Finance", #date(2026, 1, 12), null, 1},
{"E05", "Warehouse", #date(2022, 9, 1), #date(2026, 1, 31), 1},
{"E06", "Warehouse", #date(2026, 2, 2), null, 0.5},
{"E07", "Sales", #date(2026, 3, 9), null, 1}
}
),
MonthEnds = List.Transform({0..3}, each Date.EndOfMonth(Date.AddMonths(#date(2025, 12, 1), _))),
Months = Table.FromList(MonthEnds, Splitter.SplitByNothing(), type table [Month End = date]),
ActiveAt = (d as date) => Table.RowCount(Table.SelectRows(Employees, each [Start] <= d and ([Leave] = null or [Leave] > d))),
Summary = Table.AddColumn(Months, "Stats", (m) =>
let
MonthStart = Date.StartOfMonth(m[Month End]),
o = ActiveAt(Date.AddDays(MonthStart, -1)),
s = Table.RowCount(Table.SelectRows(Employees, each [Start] >= MonthStart and [Start] <= m[Month End])),
l = Table.RowCount(Table.SelectRows(Employees, each [Leave] <> null and [Leave] >= MonthStart and [Leave] <= m[Month End])),
c = ActiveAt(m[Month End])
in
[Opening = o, Starters = s, Leavers = l, Closing = c]),
Expanded = Table.ExpandRecordColumn(Summary, "Stats", {"Opening", "Starters", "Leavers", "Closing"}),
Typed = Table.TransformColumnTypes(Expanded, {{"Opening", Int64.Type}, {"Starters", Int64.Type}, {"Leavers", Int64.Type}, {"Closing", Int64.Type}}),
Check = Table.AddColumn(Typed, "Balances", each [Opening] + [Starters] - [Leavers] = [Closing], type logical)
in
CheckResult
| Month End | Opening | Starters | Leavers | Closing | Balances |
|---|---|---|---|---|---|
| 2025-12-31 | 4 | 0 | 0 | 4 | true |
| 2026-01-31 | 4 | 1 | 1 | 4 | true |
| 2026-02-28 | 4 | 1 | 1 | 4 | true |
| 2026-03-31 | 4 | 1 | 0 | 5 | true |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Every month balances: December opens and closes at 4 with no movement, January and February each have one starter and one leaver, and March has one starter and closes at 5. The Balances column is true on all four rows.
The four figures come back as a record per month, and Table.ExpandRecordColumn turns the record fields into columns. One trap here: the inner variables are named o, s, l and c, not Opening, Starters and so on. Inside a record literal, [Opening = Opening] refers to the field itself, and Power Query reports a cyclic reference error in every cell.
Why does the check balance? Because opening and closing call the same ActiveAt function, and the leaver test (leave date within the month) matches the closing test (leave date after the month end). If you change the month-end rule from > to >=, change the leaver window too, and the Balances column tells you straight away whether you got it right. A check like this is cheap to keep in the final query; the lesson on validating data and failing loudly shows how to turn a false into a refresh error.
Catch bad rows before they reach the count
Headcount errors usually come from the data, not the formula. The table at the end of this section lists three problems worth testing for on every refresh. The first, a leave date before the start date, is the easiest to test:
let
Employees = #table(
type table [ID = text, Start = date, Leave = nullable date],
{
{"E01", #date(2024, 3, 1), null},
{"E08", #date(2026, 3, 2), #date(2026, 3, 2)},
{"E09", #date(2026, 2, 10), #date(2026, 1, 30)}
}
),
Problems = Table.SelectRows(Employees, each [Leave] <> null and [Leave] < [Start])
in
ProblemsResult
| ID | Start | Leave |
|---|---|---|
| E09 | 2026-02-10 | 2026-01-30 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
This returns E09, whose leave date is before the start date: a typing error, or a cancelled hire recorded as a leaver. E08 starts and leaves on the same day, which is unusual but possible (a first-day withdrawal), so it isn't flagged; it will count as one starter and one leaver in March.
The other two checks are about keys and types. All three are summarised here:
| Problem | What it does to the report | Test |
|---|---|---|
| Leave before start | Person never counts as active, but appears as a starter and a leaver, so the Balances check fails | [Leave] < [Start] |
| Duplicate employee ID | Same person counted twice | Group by ID, count rows, keep counts above 1 |
| Leave date stored as text | Comparison fails or every row errors | Check the column type after the change-type step |
A rehire is the case that looks like a duplicate but isn't. If someone leaves and returns, the HR system may export two rows with the same ID and different start dates. That's correct for headcount (they were employed in two separate periods), so test for duplicates on ID plus start date, not on ID alone. The lesson on removing duplicates on the right key works through how to choose that key.
Turning the counts into a report
Once the month-end table exists, the rest is presentation. Load the opening, starters, leavers and closing table to a sheet for the HR team, and the department table to a PivotTable or a Power BI visual. Headcount by department is also a common driver when you allocate shared costs across departments. A few choices make the report easier to maintain:
- Generate the month list from a parameter or a date table, not a typed range, so next month appears without editing the query. An Excel cell used as a Power Query parameter lets the HR team set the end month themselves. The guide to building a date table shows a calendar that extends itself.
- Keep the active rule in one function and reference it from every query that needs it, so a change to the month-end rule happens in one place.
- Report turnover only with an agreed definition. A common form is leavers in a period divided by average headcount, but organisations differ on which leavers count (voluntary only, or all) and how the average is taken. Write the definition on the report.
For a monthly report that feeds a dashboard, the guide on Power Query M vs DAX helps decide whether month-end counts belong in the query or in a measure.
Practise the steps
The steps here are date logic, per-row filtering and grouping. The lesson on date parts and durations and the lesson on month starts, month ends and period keys cover the date functions. For grouping practice, try Summarise orders by region, and for date arithmetic try Calculate age in completed years on a report date, which uses the same "as at a date" thinking. Power Query Exercises checks each answer in the browser against hidden test data, so a rule that only works on the sample is caught.
Frequently asked questions
Should contractors and agency staff be in the headcount?
That's a reporting decision, not a Power Query one. If the export includes them, add an employment type column and filter or split on it, so the report states which groups it counts.
How do I show average headcount for a month?
Average the opening and closing figures for a quick approximation, or count active employees on every day of the month and average those counts for an exact figure. The daily version is the same ActiveAt function applied to a list of days.
Can the report handle transfers between departments?
Not from a single department column, because it only holds the current department. You need a history table with one row per assignment (department, from date, to date), and the same active test applied to each assignment row instead of each employee.
Sources
- Date.EndOfMonth (learn.microsoft.com)
- Date.AddMonths (learn.microsoft.com)
- Table.SelectRows (learn.microsoft.com)
- Table.ExpandRecordColumn (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Allocate shared costs across departments with Power QueryAllocate rent, IT and other shared costs in Power Query: driver tables, department shares, a merge that spreads each cost, and rounding that ties out.
- Build an aged receivables report with Power QueryBuild an aged debtors report in Power Query: days overdue from a fixed as-of date, ageing buckets from a table, part payments and one row per customer.
- Stock on hand from inventory movements in Power QueryTurn a log of receipts, issues, transfers and adjustments into stock on hand, stock at any date, running balances and month-end snapshots in Power Query.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.