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:
- Start as date
- End as date
- 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 😉
Community Champion
