Showing results for 
Search instead for 
Did you mean: 

Date.Networkdays function for Power Query and Power BI

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




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) =>
    // 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)



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 😉


It worked like a charm! Thanks dude 🤝


This is exactly what I need but I've never created a custom function before so I don't understand the steps to build it or how to apply it to achieve a column in an existing query.  Are there articles you would recommend to help get started?

Hi @Anonymous ,
please check out this video: Reuse M code (M functions) created by others easily - YouTube

Hi ImkeF,

This function is briliant, however I am in the same boat as the user above. I am new to using custom functions so I suspect I'm doing something wrong. I cannot figure out how to select my [Bank Holidays JSON] table after adding a new column with 'Invoke Custom Function'


If I try to use the function directly, it works, I am able to select a holidays query and date field:




However, after navigating to a different query [HH Questionnaire Dates] and adding a new column with 'Invoke Custom Function' I can no longer select the holidays table or field.

The dropdown box turns a different colour (now white instead of dark grey) and it does not do anything when clicked 😞


invoked function.png


I have tried the shorter code posted on this page and also a longer version found here:
Date.Networkdays function for Power Query and Power BI – (

The Curbal video linked above didn't suggest how to get around this
Any advice appreciated


All the best


Apologies, I figured out the problem. My date field in table [Bank Holidays JSON] was formated as a string not a date. As soon as I corrected this it worked

I believe I got this method to work; however, it seems to be converting my negatives into positives. I have student start dates in one column, and the date they received their first phone call in another. So the phone call could happen before or after their start date. If it is negative, it could result in 0, I just need to verify that they are receiving their phone call prior to their 5th day of school (so no weekends, holidays).

What is your favorite Power BI feature release for September 2023?