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.

NETWORKDAYS function

This function’s first 3 parameters work just like the Excel function and there is a 4th parameter that allows adjusting the day on which the week shall start:

  1. Start as date
  2. End as date
  3. optional holidays as list of dates
  4. optional number to change the start of the week from Monday (default: 1) to any other day (2 would mean that the week starts on Tuesday instead)

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

Date.Networkdays function for Power Query and Power BI

UI for NETWORKDAYS function for Power Query

Date.Networkdays function for Power Query and Power BI

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

Twists

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

Holiday as a column within a complete Calendar table

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

Enjoy & stay queryious ­čśë

Comments (3) Write a comment

  1. I appreciate you sharing the code. I’m having an issue with it not recognizing the parameters when I paste the code into a blank query to create the function.

    Reply

    • Sorry, but I don’t understand the issue: What do you mean with “not recognizing the parameters?”
      If you paste the code into a blank query and press OK, that query should be converted to a function.
      You then reference that function in your further queries.
      Otherwise please try to give some more details, thanks.

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz