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 😉

[Ry009]

My date field for holidays table is formatted as a date, but still getting a null/white dropdown when trying to select the holidays when invoking the function.

Not sure what I'm doing wrong?

[sbschultz]

I am using this formula to calculate the networking days between a number of date stamps in a data set to track the time at various check points in a process - so I am invoking it about 9 times in my query. However this seems to slow down the performance of my queries when they refresh. It was taking about an hour to refresh at one point - I turned off the background refresh which seems to have halved the time but 30 minutes is still quite long.

My data sources are all tables in the same file as my query and not an external data source. There are quite a few joins, but it all runs fine until I invoke the formula a few times.