Hi everyone,
I have a scenario where I want to extract number of leave hours in each month. In picture below you can see that first employee has leave hours of 1800 hours spread across 10 months. I want to get number of hours in each month using PQ as customer wants to do it in excel? Any help will be much appreciated.
sample file.xlsx (8.5 KB)A few questions:
Thanks.
Hi @BrianJ,
Thank you for getting back so quickly. You have very valid questions and I should have mentioned in the post itself. The answer of your first 2 question is yes that it is 8 hr/day M/F and it incorporates public holidays.
Now I want data in below format. You can see that we have 7 working days(3 in November and 4 in December), so no. of hours should split in 2 rows. Does that make sense?
Regards,
Hafiz
Sorry - two more questions:
Thanks.
Hi @BrianJ,
This is for New Zealand and I have extracted public holidays from 2016 to 2021 in attached file. You are right regarding excel. But only issue is that customer will feed this information into one other tools which is adaptive insight. So, that is why they want to format is in this way and then use adaptive insight. If we can use power query to format, we can load this to excel table and then feed it into adaptive insight.
Kind Regards,
HafizHolidays.xlsx (14.4 KB)
Hi @hafizsultan,
With the excellent prepping already done by @BrianJ I’ve got a solution for you.
( myStartDate as date, myEndDate as date, myLeave as number, myHolidayList as list ) as table =>
let
// load the Holidays into memory
HolidayList = List.Buffer(myHolidayList),
DateRange =
List.Buffer(
List.Select(
List.Difference(
List.Dates(myStartDate, Number.From(myEndDate - myStartDate) +1, Duration.From(1)),
List.Transform(HolidayList, Date.From )),
each Date.DayOfWeek(_, 1) < 5 )
),
GenerateList = List.Generate(
// Create an initial Record
() => [Date = DateRange{0}, hLeave = myLeave-8, i = 0],
// Define the Condition (Do-While-Loop)
each [hLeave] > 0 and List.Count(DateRange)-1 >= [i],
// Function that defines a new value for each Record element in that iteration.
each [Date = DateRange{i}, i = [i] +1, hLeave = [hLeave]-8 ]
),
CreateTable = Table.FromRecords(GenerateList),
AddMonthColumn = Table.AddColumn(CreateTable, "Month", each Text.Proper( Date.MonthName([Date])) & ", " & Text.From(Date.Year([Date])), type text),
GroupRows = Table.Group(AddMonthColumn, {"Month"}, {{"Leave Hours p/m", each Table.RowCount(_)*8, Int64.Type}}),
AddCheckHours = Table.AddColumn(GroupRows, "Check Hours", each List.Sum(GroupRows[#"Leave Hours p/m"]), Int64.Type)
in
try AddCheckHours otherwise #table( {}, {} )
This function has 2 limits. It stops when the leave hours go negative OR when the set date range limit is reached. Another thing to note is that it’s designed for full 8 hour days it doesn’t return a remainder.
with this result.
As you can see my results don’t match your and that’s because you didn’t seem to account for the holiday in November…
Here’s your sample file. eDNA - Calculate Leave Hours per Month.xlsx (215.9 KB)
I hope this is helpful.
Great solution and perfect timing. I just dropped into this thread to tell @hafizsultan that I fell down a rabbit hole until 4 AM(!) on another one, which looked simple and decidedly was not, and as a result was going to be delayed on the solution for this one.
@hafizsultan - congratulations on being the first eDNA Power User!
– Brian
That’s an incredible solution. I will test it tomorrow with my dataset and will come back with the testing result. Can you please explain what is " date range limit" in our limitations?
Thank you mate. Your provide tremendous support to everyone in this group, which is amazing. I want to involve more on this forum to learn from experts like you ,Sam and Mellisa and hopefully I will be able to spend more time to improve my skills.
Kindest Regards,
Hafiz
Hi @hafizsultan,
The date range limit is determined by the START_DATE and FINISH_DATE.
A list of all dates is generated including both the start- and end date, then weekends and holidays are excluded. What remains is a list with only valid dates for the selected record.
Date range limit sounds fine. Thanks for clarification.
I have tried it but unfortuantely it is not providing the same results. May be I am missing something. E.g I got 2 issues here:
Also I was thinking something around like during these times, some people are working flexible hours, so they may have reduced their time to 4 hours. Can we incorporate this information somehow either by:
I have create PBIX file for simplicity. Please find attached PBIX file and sample excel file and guide me to solve this mystery 
Kind Regards,
Hafizsample PBI file.pbix (78.7 KB) sample file.xlsx (13.3 KB)
Please ignore my previous email. I have applications support background, so I like to have tendency to look for probable cause. Here in the script, I changed >0 to >=0 and it worked as I worked out that 8-8 would be 0, so we should have >=0 condition and it is working fine for me.
The only thing that I would like to bring in that if employee has applied 4 hours of leave, then we should bring those too. How can we accomplish that?
Kind Regards,
Hafiz
Hi @hafizsultan,
Good catch on the condition 
Love it when I see people dive into the M code, figuring out what is going on…
I’ve amended the function fxSplitLeaveHours further and added a new column to the output table. The condition in now > -8 so we can also catch partial leave days taken.
( myStartDate as date, myEndDate as date, myLeave as number, myHolidayList as list ) as table =>
let
// load the Holidays into memory
HolidayList = List.Buffer(myHolidayList),
DateRange =
List.Buffer(
List.Select(
List.Difference(
List.Dates(myStartDate, Number.From(myEndDate - myStartDate) +1, Duration.From(1)),
List.Transform(HolidayList, Date.From )),
each Date.DayOfWeek(_, 1) < 5 )
),
GenerateList = List.Generate(
// Create an initial Record
() => [Date = DateRange{0}, hLeave = myLeave-8, i = 0, h = if myLeave >8 then 8 else myLeave],
// Define the Condition (Do-While-Loop)
each [hLeave] >-8 and List.Count(DateRange)-1 >= [i],
// Function that defines a new value for each Record element in that iteration.
each [Date = DateRange{i}, i = [i] +1, hLeave = [hLeave]-8, h = if hLeave <0 then hLeave+8 else 8]
),
CreateTable = Table.FromRecords(GenerateList),
AddMonthColumn = Table.AddColumn(CreateTable, "Month", each Text.Proper( Date.MonthName([Date])) & ", " & Text.From(Date.Year([Date])), type text),
GroupRows = Table.Group(AddMonthColumn, {"Month"}, {{"Leave Hours p/m", each List.Sum([h]), type number}}),
AddCheckHours = Table.AddColumn(GroupRows, "Check Hours", each List.Sum(GroupRows[#"Leave Hours p/m"]), Int64.Type)
in
try AddCheckHours otherwise #table( {}, {} )
.
Added a FileLocation parameter to your file just select yours from the list and the queries will restore. Here’s the updated file. eDNA - Leave Hours.pbix (84.7 KB)
I hope this is helpful.
This is a perfect solution and it is working like a charm. I am over the moon
Thank you so much.
Kind Regards,
Hafiz
It was an interesting scenario, loved working with you on this.
All the best Hafiz