Date.Networkdays function for Power Query and Power BI

[ImkeF]

Today I'm going to share my custom NETWORKDAYS function for Power Query with you that uses the same syntax than its Excel-equivalent.

NETWORKDAYS function

This function has 3 parameters:

1. Start as date
2. End as date
3. optional holidays as list of dates

The function comes with a UI that lets you first choose a table containing the holidays and then choose the column with the holiday date:

UI for NETWORKDAYS function for Power Query

Select date column for NETWORKDAYS function

But you can also type in the list of holidays in manually. Therefore leave the optional parameter blank if you use it through the UI and edit the formula afterwards like so:

fnNETWORKDAYS ( StartDate, EndDate, {#date(2020, 1, 1) {#date(2020,12,25)} )

...adding all necessary dates into the 3rd parameters list.

The Code

(StartDate as date, EndDate as date, optional Holidays as list) =>
let
    // Get list of dates between Start- and EndDate
    ListOfDates = List.Dates(StartDate,Number.From(EndDate-StartDate),#duration(1,0,0,0)),

    // if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays;
    // otherwise continue with previous table
    DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, Holidays),

    // Select only the first 5 days of the week 
    // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week
    DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_,1) < 5 ),

    // Count the number of days (items in the list)
    CountDays = List.Count(DeleteWeekends)
in
    CountDays

Twists

If your holidays don't sit in a dedicated table but in separate column of a calendar table like so:

I'd recommend to reference that table, filter only holidays and then reference it's data column like mentioned before.

Enjoy & stay queryious 😉