Forum Discussion
Calculating Business days excluding holidays function with null values
I need a function in power query to calculate the number of business days, excluding holidays and accounting for any null date values. I also need it to replace any results <0 with null, or replace any values=0 with 1.
I have edited a function posted by Anonymous .
This is the error.
This is the function producing the error.
let
fBusDays = (StartDate as date, EndDate as date, HolidayList as list) as number=>
let
varBusDays = if StartDate = null or EndDate = null then null else
//create series of Dates
DateList=List.Dates(StartDate,Number.From(EndDate-StartDate),#duration(1,0,0,0)),
//remove weekends
RemoveWeekends=List.Select(DateList,each Date.DayOfWeek(_,Day.Monday)<5),
//remove holidays
RemoveHolidays=List.RemoveItems(RemoveWeekends, HolidayList),
//count days
Busdays=List.Count(RemoveHolidays),
result =
if varBusDays = null then null
else if varBusDays <0 then null
else if varBusDays = 0 then 1
else varBusdays
in
result
in
fBusdaysThe Advanced Editor does not give me any syntax errors, so I am quite lost.
TIA
Try this
let
fBusDays = (StartDate as nullable date, EndDate as nullable date, HolidayList as list) as nullable number =>
let
varBusDays =
if StartDate = null or EndDate = null then
null
else
let
// Ensure end date is included by adding 1 to the count
DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, HolidayList),
BusDays = List.Count(RemoveHolidays)
in
if BusDays < 0 then null
else if BusDays = 0 then 1
else BusDays
in
varBusDays
in
fBusDays
You can invoke it like this in a custom column:
fBusDays([Start Date], [End Date], HolidayList)
Make sure HolidayList is a list of dates.
6 Replies
Try this
let
fBusDays = (StartDate as nullable date, EndDate as nullable date, HolidayList as list) as nullable number =>
let
varBusDays =
if StartDate = null or EndDate = null then
null
else
let
// Ensure end date is included by adding 1 to the count
DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, HolidayList),
BusDays = List.Count(RemoveHolidays)
in
if BusDays < 0 then null
else if BusDays = 0 then 1
else BusDays
in
varBusDays
in
fBusDays
You can invoke it like this in a custom column:
fBusDays([Start Date], [End Date], HolidayList)
Make sure HolidayList is a list of dates.
Thank you so much! It works. 😀
I know I marked this thread a resolved, but I have one more condition that produces errors in this function. And that is when the Start Date is greater than the End Date (unfortunatley, the data I have contains this scenario).
The function produces an Error. I can manually replace the errors with null by going to the “Transform” tab then selecting the drop down for “Replace Values” and choosing “Replace Errors, ” but was wondering if there is a way to edit the function to resolve them.
Thank you again for your help.
I was able to resolve the issue of Start greater than End Date by modifying the function as follows:
let fBusDays = (StartDate as nullable date, EndDate as nullable date, HolidayList as list) as nullable number => let varBusDays = if StartDate = null or EndDate = null or StartDate>EndDate then null else let // Ensure end date is included by adding 1 to the count DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)), RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5), RemoveHolidays = List.RemoveItems(RemoveWeekends, HolidayList), BusDays = List.Count(RemoveHolidays) in if BusDays < 0 then null else if BusDays = 0 then 1 else BusDays in varBusDays in fBusDays
Hi Txtcher
You're super close —
honestly just a tiny typo is causing your problem.Here’s exactly what’s wrong:
🔴 You have mismatched variable names:-
You create
varBusDays(with capital D) -
Then in the final
in, you writefBusdays(lowercased).
Power Query is case sensitive, so
fBusdaysandfBusDaysare not the same for it!That's why it's breaking even though it looks fine to us.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
-
Thank you. So here is the edited function:
let fBusDays = (StartDate as date, EndDate as date, HolidayList as list) as number=> let varBusDays = if StartDate = null or EndDate = null then null else //create series of dates DateList=List.Dates(StartDate, Number.From(EndDate-StartDate), #duration(1,0,0.0)), //remove weekends RemoveWeekends=List.Select(DateList, each Date.DayOfWeek(_, Day.Monday)<5), // remove holidays RemoveHolidays=List.RemoveItems(RemoveWeekends, HolidayList), // count days BusDays=List.Count(RemoveHolidays), result = if varBusDays = null then null else if varBusDays <0 then null else if varBusDays = 0 then 1 else varBusDays in result in fBusDaysBut when I invoke it with a custom column, I get nothing but errors:
???
