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 (23) 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

  2. Hello Imke,
    Thank you for the awesome function. I am having one problem though.
    My end date is in a different query so that it can be updated monthly without opening the Edit Query window.
    Is there a way to reference the other query from your function without triggering the Formula.Firewall error?

    Thank you.

    Reply

    • Hi Matthew,
      you can try to functionize the reference.

      Create a new query (YourNewFunctionQuery) that reference the DifferentQuery. Navigate to the “end date”.
      Go to the advanced editor and put ” ()=> ” in front of the code in there.
      That will transform it into a function (without a parameter).

      You can invoke it with empty brackets like so: YourNewFunctionQuery()

      /Imke

      Reply

  3. Hello Imke,
    Thank you for the advice. I successfully transformed the reference to a function.

    However, when referencing from your function, it fails with the same error as before.
    “Formula.Firewall: Query ‘spend_calc’ (step ‘Invoked Custom Function’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”

    I think the problem is lazy evaluation used by PQ – referenced by Ken Puls (https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/), Chris Webb, and others.
    Therefore, we can’t reference a different query using a custom function.

    I ended up building the referenced query (which just pulled month start date) into the main query to pull in the data and subtracting the end date from start date. I still don’t have weekends and holidays removed though.

    Purely for experimentation, I tried changing your function data type to list for the first two parameters. This lets me choose a column in the current query as start date and a different column as end date. Unfortunately, changing your function data types caused a different error, “Expression.Error: We cannot convert a value of type List to type Date.”

    If there is a way to adjust your function to accept a List instead of a date, that would be ideal. I need it to not have a fixed start or end date, but accept the date from an existing column in the current query.

    Apparently, I just don’t know enough about M functions. I will study more.

    Thank you for your time.
    Matthew

    Reply

  4. Hi Imke,
    thanks so much for this, it’s very clear & what I have been looking for!

    The only challenge I have is that when calculating the difference between 2 date columns, Power Query returns my result with the format “1.00:00:00” instead of “1”

    This means I get this error when using the custom function:

    Expression.Error: The ‘increment’ argument is out of range.
    Details:1.00:00:00

    Reply

    • Sorry Scott, but I cannot follow.
      My function returns the result of a List.Count function. So that will always be a number.
      What are you referencing to that returns a duration?
      If you have a column with a start date and another with an end date you should feed them as the first 2 arguments into my function.
      /Imke

      Reply

    • @Scott I get the same thing. I have two columns with date (type date dd-mm-yy) and then for some records i get error

      Expression.Error: The ‘increment’ argument is out of range.
      Details:1.00:00:00

      i can not figure out what the problem is as it works for some rows and it worked on other ocasionas before even with the same columns

      @Imke I really like the function but for above reason I am not able to use it. Also the UI for the Holidays doesnt work for me.. Do I need to enable some preview functionality for this function to work ?

      Reply

    • @ Scot .. the function only works if Start Date < End Date so it is not entirely like NETWORKDAYS but the easies work around is to do something like
      if END DATE<START DATE
      then fnNETWORKDAYS(END DATE, START DATE, null, null)
      else
      if START DATE<END DATE
      then fnNETWORKDAYS(START DATE, END DATE, null, null)
      else 0
      //with the 0 I covered the issue i have when start date and end date are same date.

      @Imke If you have any ideas how to incorporate it into the function that way that i would be able to calculate the networkdays also if the end date is before start date .. your help would be very much appreciated.

      thank you
      m

      Reply

      • Hi Michal,
        thanks for making me aware of this.
        Have adjusted the function accordingly.
        /Imke

        Reply

        • hi @Imke I really like your “adjustment” i will test it but from the code it looks clever.

          I have another question suggestion. In logistics when start and end date is the same it is considered as same day delivery and the value should be 0 , another example next day delivery is lets say start day We 6th of May end day Th 7th of May with NETWORKDAYS in excel and also your M version, this would give me value of 2, but i would like the value to be 1.

          The easiest solution that comes to my mind is just substract 1 or – 1 based on the fact if the result is negative or positive number. But that would make the step for that column complex to read …. is there maybe a way that this could be build in your function or to be added as an optional parameter??

          Thank you .. really appreciate your help

          m.

          Reply

          • Hi michal,
            I’d prefer to keep this formula in accordance with the Excel equivalent.
            /Imke

  5. Hi @Imke .. Ok .. fair point.. 🙂 I have one question/feedback for the optional parameter holidays .. when try to add column using this function .. and click on the select column for Holidays(optional) the UI doesn’t offer me any options .. Is it something that i am doing wrong ? I actually get it to work one or two times but then i just get no options even though i have there several tables/query… what I am trying to accomplish is the previous scenario where the parameters for your function would be something like (startdate, enddate, startdate ->as the holiday optional parameter, null) .. you can do it in excel so that’s why i was trying to experiment with this logic but i can not get that option to work at all..
    on another note can you recommend a good solution how to deal with the holidays where i would like to use table with multiple columns (holidays for different countries) and then use your function with some kind of if statement that would look up the country and then remove the holidays accordingly ..

    thank you

    /m

    Reply

    • Hi Michal,
      no idea why that dialogue doesn’t show up. But you can simply reference it by using some code like so: #”YourTableName”[YourColumnName]
      As for a table with multiple columns for each country: You can unpivot it so all values with in one column. Or if it is a table with just holidays and you want all columns to be considered, use List.Combine(Table.ToColumns(#”YourTableName”)).
      This will first turn your table into a list of nested lists (for each column) and then combine them all to on big list.
      /Imke

      Reply

        • Hi Jerry,
          the dropdown won’t operate if there are no tables in your file yet.
          Otherwise I cannot imagine what the reason could be.
          /Imke

          Reply

    • Yes, English is not my first language, so you will probably find many of them in my posts.
      I decided to focus on the technical aspects and don’t invest too much time in language polishing.

      Reply

  6. Hello
    For me, the working days are Sunday to Thursday
    And Friday and Saturday are days of rest
    How can I change this?

    Reply

Leave a Reply

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