How many people used their entire sick leave entitlement? [Power Query / Excel homework]
I have a big day tomorrow. We are celebrating our kids (Nishanth & Nakshatra) 9th birthday this weekend. We (Jo & I) must have been possessed by demons, for we are hosting the party at our home. 12 kids (half of each kind) will be invading our house for a couple of hours. There will be balloons to blow, cakes to bake, decorations to dangle, meals to make, children to cheer, guests to greet, dishes to wash, carpets to clean, walls to varnish, furniture to fix and Chandoo to calm.
So let’s keep this quick and simple. I want you to figure out an elegant and simple way to answer below questions.
Imagine you are the HR analyst at BigLargeCompany. You need to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.
You have two tables – emps & leaves as illustrated below.
Your mission is to find out answers to below questions.
 How many employees used exactly 100% of their entitled sick leave?
 How many employees did not take any sick leaves?
 Listing of all employees who used 100% of their entitlement
Use either Power Query, Excel formulas or any other technique to answer the questions.
Please download sample data for this exercise here.
Once you have answers, post them in comments section.
Want more problems? Check out Excel homework section for some very tricky, interesting challenges.
Talk to you soon. If you need me, I will be scraping ketch up off carpets.
Update: Solution is here
If you want to learn how to solve problems like this, check out the Entitlement vs. usage analysis – solution page.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or may be join my online video class to master Excel.
Thank you and see you around.
Related articles:

Leave a Reply
« Lenient lookup [Advanced Formula Trick]  Leave entitlement vs. usage analysis with Power Query » 
17 Responses to “How many people used their entire sick leave entitlement? [Power Query / Excel homework]”
PQ  On leaves, Group by [Emp ID] and aggregate [Days used] by sum.
Left Join to emps.
Add custom column.
= try [Days used]/[Entitlement] otherwise 0
Then use pivot table(s) to summarize (using slicers). Alternately use custom functions to aggregate data to return single value (Table.SelectRows, Table.RowCount).
If you have PowerPivot (DAX) available. There are number of ways to set up model for this data. 1 & 2 are preferable over 3.
1. Create single flat table as above. Use DAX measures and cube functions to summarize.
2. Create 2 separate dimension from emps, 1 for unlimited entitlement and another for limited entitlement. Report using multiple visual/pivottable.
3. Load both to model after Group by operation. Add calculated columns to leave table to handle unlimited entitlement.
Here's DAX solutions using model based on option 2.
No absence:
No Abs:=COUNTROWS(Unlimited)+COUNTROWS(Limited)COUNTROWS('leaves')
Exactly 100%:
Count Exact:=COUNTX(FILTER('leaves',RELATED(Limited[Emp ID])BLANK()),IF([Days used]/RELATED(Limited[Entitlement])=1,1,Blank()))
100% & Over Count:
Count 100% and Over:=COUNTX(FILTER('leaves',RELATED(Limited[Emp ID])BLANK()),IF([Days used]/RELATED(Limited[Entitlement])>=1,1,Blank()))
For the list, used Pivot Table (you can use CONCATENATEX... but that'd make it hard to read).
For some reason, "=" between RELATED() and BLANK() was removed from measure. You'll need it for it to work.
21, 10, 67
How many employees used exactly 100% of their entitled sick leave? = 21.
How many employees did not take any sick leaves? = 10.
Listing of all employees who used 100% of their entitlement = EMP IDs  35, 77, 78, 273, 276, 281, 301, 316, 338 & 416.
I used PQ to solve the problem
File link: https://1drv.ms/x/s!AmoScH5srsIYvRntB_Cm_U53nFh2
1. Leave used exactly 100% : 21
2. Didn't take any leave : 10
3. Used 100% of the entitlement : 67
List (5,7,12,30,32,34,43,50,61,79,89,90,91,93,101,103,104,105,119,128,131,139,141,150,157,170,175,176,177,178,179,186,188,191,215,225,231,238,240,249,252,257,259,278,288,295,298,306,312,313,317,318,323,324,327,334,342,344,345,346,370,379,381,387,392,401,415)
Exactly 100% = 21
=SUMPRODUCT((SUMIFS(leaves[Days used],leaves[Emp ID],emps[Emp ID])=emps[Entitlement]))
At Least 100% = 67
=SUMPRODUCT((SUMIFS(leaves[Days used],leaves[Emp ID],emps[Emp ID])>=emps[Entitlement]))
None = 10
=SUMPRODUCT((SUMIFS(leaves[Days used],leaves[Emp ID],emps[Emp ID])=0))
Getting the list required something more than regular functions. I used a personal VBA function that returns a delimited list of multiple matches and chose to obtain those IDs for the Exactly 100% case.
7; 12; 91; 103; 104; 131; 157; 170; 176; 177; 225; 249; 295; 298; 313; 317; 342; 346; 381; 392; 415
Create a query for each table ... I chose Close and Load to the same worksheet in each case
Now Append leaves query to emps query to give me a three column query: Emp ID, Entitlement, Spent
Use this new query to create a Pivot Table:
Emp ID in Rows
For demonstration I put Entitlement and Spent in the Values area ... noting that Entitlement will show as Count because there are text entries in that column so you have to make it show Sum
I now created a Calculated Field called Entitl less Spent to show the Balance on the Leave Account for each employee
From that column, my answers:
21 people used all of their entitlement
Emloyees who took no leave:
35
77
78
273
276
281
301
316
338
416
The 21 people who have taken all of their entitlement are:
7
12
91
103
104
131
157
170
176
177
225
249
295
298
313
317
342
346
381
392
415
If I am wrong here, please point out my errors, I would be grateful!
1. Employees who used 100% of their entitlement = 67;
=SUMPRODUCT((SUMIF(leaves[Emp ID],
emps[Emp ID],
leaves[Days used])>=emps[Entitlement]))
2. Employees who took no sick leave = 10;
=SUMPRODUCT((SUMIF(leaves[Emp ID],
emps[Emp ID],
leaves[Days used])=0))
3. List of 67 employees in Item 1, starting with cell O7 downwards;
=IFERROR(LOOKUP(1,
1/(1/ROW(emps[Emp ID])=MAX(INDEX(1/ROW(emps[Emp ID])*(SUMIF(leaves[Emp ID],emps[Emp ID],leaves[Days used])>=emps[Entitlement])*(COUNTIF(O$6:O6,emps[Emp ID])=0),0))),
emps[Emp ID]),"")
Note: 1/ROW(emps[Emp ID]) is the imaginary helper column and (COUNTIF(O$6:O6,emps[Emp ID])=0) filters for employees not yet listed.
Load both tables as connection only
1) emp
let
Source = Excel.CurrentWorkbook(){[Name="emps"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", type text}}),
FilterOutX = Table.SelectRows(#"Changed Type", each ([Entitlement] "x")),
ChangeToNumber = Table.TransformColumnTypes(FilterOutX,{{"Entitlement", Int64.Type}})
in
ChangeToNumber
2) leaves, already grouped to sum the days used
let
Source = Excel.CurrentWorkbook(){[Name="leaves"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Days used", Int64.Type}, {"Emp ID", type text}}),
SumOfDaysUsed = Table.Group(#"Changed Type", {"Emp ID"}, {{"Days Used", each List.Sum([Days used]), type number}}),
SetAsNumber = Table.TransformColumnTypes(SumOfDaysUsed,{{"Days Used", Int64.Type}})
in
SetAsNumber
3) Analysis: I appended both tables, sorted, grouped to sum, calculated remaining, defined categories, filter the categories and extracted the list of ID's.
let
Source = Table.Combine({emps, leaves}),
SortOnID = Table.Sort(Source,{{"Emp ID", Order.Ascending}}),
SumValues = Table.Group(SortOnID, {"Emp ID"}, {{"Entitlement", each List.Sum([Entitlement]), type anynonnull}, {"Days Used", each List.Sum([Days Used]), type number}}),
FilterOutUnlimted = Table.SelectRows(SumValues, each ([Entitlement] null)),
CalcRemaining = Table.AddColumn(FilterOutUnlimted, "Remaining", each if [Entitlement]=null then null else [Entitlement][Days Used]),
DefineCategory = Table.AddColumn(CalcRemaining, "Category", each if [Remaining] = 0 then "All sick days taken" else if [Days Used] = null then "No sick days taken" else if [Remaining] < 0 then "More sick days taken then entitled" else "Some days taken"),
FilterOnScope = Table.SelectRows(DefineCategory, each ([Category] = "All sick days taken" or [Category] = "No sick days taken")),
GroupCategory = Table.Group(FilterOnScope, {"Category"}, {{"AsTable", each _, type table}, {"Count Employees", each Table.RowCount(_), type number}}),
ListIDs = Table.AddColumn(GroupCategory, "List IDs", each if [Category] "All sick days taken" then null else List.Accumulate(Table.Column([AsTable],"Emp ID") ,"", (state,current) => if state = "" then current else state & ", " & current ))
in
ListIDs
Analysis loaded to workbook:
 21 employees took exactly 100% of entitled sickness.
 List is {7, 12, 91, 103, 104, 131, 157, 170, 176, 177, 225, 249, 295, 298, 313, 317, 342, 346, 381, 392, 415}
 10 have taken no sick leave
Okay, I misread the assignment
My corrected analysis query
let
Source = Table.Combine({emps, leaves}),
SortOnID = Table.Sort(Source,{{"Emp ID", Order.Ascending}}),
SumValues = Table.Group(SortOnID, {"Emp ID"}, {{"Entitlement", each List.Sum([Entitlement]), type anynonnull}, {"Days Used", each List.Sum([Days Used]), type number}}),
FilterOutUnlimted = Table.SelectRows(SumValues, each ([Entitlement] null)),
CalcRemaining = Table.AddColumn(FilterOutUnlimted, "Remaining", each if [Entitlement]=null then null else [Entitlement][Days Used]),
DefineCategory = Table.AddColumn(CalcRemaining, "Category", each if [Remaining] = 0 then "Exactly all sick days taken" else if [Days Used] = null then "No sick days taken" else if [Remaining] < 0 then "More sick days taken then entitled" else "Some days taken"),
FilterOnScope = Table.SelectRows(DefineCategory, each ([Category] "Some days taken")),
AddCat2 = Table.AddColumn(FilterOnScope, "Category2", each if [Category] = "No sick days taken" then [Category] else "All entitled sick days taken"),
GroupCategory = Table.Group(AddCat2, {"Category2", "Category"}, {{"AsTable", each _, type table}, {"Count Employees", each Table.RowCount(_), type number}}),
ListIDs = Table.AddColumn(GroupCategory, "List IDs", each if [Category2] "All entitled sick days taken" then null else if [Category] = "Exactly all sick days taken" then null else List.Accumulate(Table.Column([AsTable],"Emp ID") ,"", (state,current) => if state = "" then current else state & ", " & current )),
AddGroupCat = Table.AddColumn(ListIDs, "Group", each if [Category] = "Exactly all sick days taken" then [Category] else [Category2]),
RemoveCol = Table.RemoveColumns(AddGroupCat,{"Category2", "Category", "AsTable"}),
GroupAsFirstCol = Table.ReorderColumns(RemoveCol,{"Group", "Count Employees", "List IDs"})
in
GroupAsFirstCol
It took me about ten minutes to sort, sum and count. How long does Power Query take?
Hi Tim,
It took roughly 6~8 minutes to use PQ to work out the answers.
I am new to PQ but I think what is more valuable is that PQ allow you to simply replace the source data to get the result in one click (refresh data). So you do not need to spend another 10 minute next time for a different set of data.
Cheers,
Terry
1) Import both tables (Emps and Leaves) to PQ;
2) Group 'Days Used' column in the Leaves table by Emp ID, and then merge the new table with Emps table, then expand the new merged column to show Entitlement;
3) Duplicate the new Leaves table, add a new column to calculate the difference between the 'Total Days Used' column and 'Entitlement' column, then filter the difference column by '0'. Remove all columns except the Emp ID column and then use the Count Rows function to find out the total number of employees who used exactly 100% of their leaves;
4) Merge the Emps table with Leaves table, expand the new merged column to show Total Days Left. Filter the column by null, Remove all columns except the Emp ID column and then use the Count Rows function to find out the total number of employees who used exactly 100% of their leaves;
5) Duplicate the new table from Step 3, alter the filter step to show all employees who have used 100% of their entitlement or more. Remove the step of Count Rows and you will have the list of employees who have no entitlement left.
The answers are 21, 10, and 67 (the answer for Q3 should be a list of employees but for simplicity I just use the total count as the answer)
I used the following formulas:
SUM(IF(SUMIFI(leaves[Emp ID],emps[Emp ID],leaves[Days used])=emps[Entitlement],1,0))
SUM(IF(SUMIF(leaves[Emp ID],emps[Emp ID],leaves[Days used])=0,1,0))
TEXTJOIN(", ",1,IF(IF(SUMIF(leaves[Emp ID],emps[Emp ID],leaves[Days used])=emps[Entitlement],1,"")=1,emps[Emp ID],""))
the results were
21
10
7, 12, 91, 103, 104, 131, 157, 170, 176, 177, 225, 249, 295, 298, 313, 317, 342, 346, 381, 392, 415
Great Article Chandoo, Thanks for sharing, Keep up the good work
Using named formulas. The first step was to sum the number of days leave for each employer, 'used':
= SUMIFS(leaves[Days used],leaves[Emp ID], ID)
where ID is a shorter name to refer to '=emps[Emp ID]'.
To avoid the nuisance of CSE or SUMPRODUCT without the product, a couple of further named formulas 'zeroSickLeave?' and 'excessSickLeave?' to act as flags:
= N(used=0)
= N(emps[Entitlement] <= used)
That leaves worksheet formulae
= SUM( excessSickLeave? )
= SUM( zeroSickLeave? )
giving 67 and 10 respectively.
The list requires the SMALL function
= IFERROR( SMALL( IF(excessSickLeave?, ID ), ID ), "" )
Roll on dynamic arrays and FILTER!