Calculating Business days excluding holidays function with null values

[Txtcher]

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 @v-rzhou-msft . 

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
  fBusdays

The Advanced Editor does not give me any syntax errors, so I am quite lost.

TIA

[Amar_Kumar · Accepted solution]

Try this

@Txtcher 

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.

[Txtcher]

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.

[Txtcher]

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

[Txtcher]

Thank you so much! It works. 😀

[Txtcher]

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 
  fBusDays

But when I invoke it with a custom column, I get nothing but errors:

???

[johnbasha33]

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 write fBusdays (lowercase d).

Power Query is case sensitive, so fBusdays and fBusDays are 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 !!