Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

ImkeF

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 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:

1_UI_Dialogue.png

 

UI for NETWORKDAYS function for Power Query

 

 

2_UI_Dialogue_Choose_Column.png

 

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:

 

3_CalendarHolidayTable.png

 

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

 

Enjoy & stay queryious 😉

Comments

It worked like a charm! Thanks dude 🤝

Anonymous

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:

 

function.png

 

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 – (thebiccountant.com)

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

 

All the best

Vicky

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).

Hi, in my dataset in some cases my second date is before the first date. The function result is than error. Is it possible to rewrite the function to have a negative number of days in this case?

 

Thanks. 

Sure @Rgieltjes_Meyn ,
actually, there is an updated version of it on my blog here: Date.Networkdays function for Power Query and Power BI – (thebiccountant.com)
that covers that scenarion as well.

@ImkeF Thanks for your reply,

 

The Function gives an error as shown below. Can you tell what is wrong?

Rgieltjes_Meyn_0-1696833828529.png

 

 

Hi @Rgieltjes_Meyn ,
I don't see an error here.
Did you paste the correct picture?

 

@ImkeF If i click on 'Show error' i think this is higlighted.

Rgieltjes_Meyn_0-1696852999630.png

 

Hi @Rgieltjes_Meyn ,
sorry, but I cannot reprocude the error.
Did you change anything in the code?
Please check that you have copy-pasted the full code in there.

Hi,

Thanks for the tuto.

It's very usefull but i have a problem with the refresh. the function take very long time and the refresh failed with time out.

Here is my code:

 

(Pays as text, optional StartDate as date, optional EndDate as date) =>

let
  HolidayList = if Pays ="Grand-Duché de Luxembourg" then LSP_CalendrierVacancesLux else LSP_CalendrierVacancesBEL,
  DateList = List.Dates(StartDate, Number.From(EndDate-StartDate)+1,#duration(1,0,0,0)),
  RemoveWeekEnds = List.Select(DateList, each Date.DayOfWeek(_,Day.Monday)< 5),
  RemoveHolidays = List.RemoveItems(RemoveWeekEnds, HolidayList),
  CountDays = if(StartDate = null or EndDate= null or Pays=null) then null else if EndDate < StartDate then 0  else List.Count(RemoveHolidays)-1
in
  CountDays
 
For my table 
let
  Source = wPeechDossiers,
  #"Lignes filtrées" = Table.SelectRows(Source, each ([EtatDossier] <> "Annulé")),
  #"Colonne conditionnelle insérée" = Table.AddColumn(#"Lignes filtrées", "DGOenPromo", each if [VenteDateF] >= [GODateD] then true else false),
  #"Lignes filtrées 1" = Table.SelectRows(#"Colonne conditionnelle insérée", each ([RDCDateF] <> null) and ([COMMBPEBPGODateF] <> null)),
  Personnalisé = Table.AddColumn(#"Lignes filtrées 1", "Workdays RDC/METRE", each WorkDays([ChantierPays], [RDCDateF], [COMMBPEBPGODateF])),
  #"Personnalisé 2" = Table.AddColumn(Personnalisé, "Workdays VNT/IPU", each WorkDays([ChantierPays], [VenteDateF],[IPUDateF])),
  #"Personnalisé 1" = Table.AddColumn(#"Personnalisé 2", "Workdays DG/DGO", each WorkDays([ChantierPays], List.Max({[DGDVDateF],[OPUDateF]}),[GODateD])),
  #"Personnalisé 3" = Table.AddColumn(#"Personnalisé 1", "Workdays IPU/OPU", each WorkDays([ChantierPays], [IPUDateF],[OPUDateF])),
  #"Personnalisé 4" = Table.AddColumn(#"Personnalisé 3","Workdays IPU/CEPI", each WorkDays([ChantierPays], [IPUDateF],[CépiDateF])),
  #"Personnalisé 5" = Table.AddColumn(#"Personnalisé 4","Workdays VNT/DGO", each WorkDays([ChantierPays], [VenteDateF],[GODateD])),
  #"Personnalisé 6" = Table.AddColumn(#"Personnalisé 5","Workdays DGO/RP", each WorkDays([ChantierPays], [GODateD],[RPDateF])),
  #"Personnalisé 7" = Table.AddColumn(#"Personnalisé 6","Workdays FGO/RP", each WorkDays([ChantierPays],[GODateF],[RPDateF])),
  #"Erreurs remplacées" = Table.ReplaceErrorValues(#"Personnalisé 7", {{"Workdays DGO/RP", ""}, {"Workdays RDC/METRE", ""}, {"Workdays DG/DGO", ""}, {"Workdays VNT/IPU", ""}, {"Workdays IPU/OPU", ""}, {"Workdays IPU/CEPI", ""}, {"Workdays VNT/DGO", ""},{"DGOenPromo", ""},{"Workdays FGO/RP", ""}}),
  #"Type de colonne changé 3" = Table.TransformColumnTypes(#"Erreurs remplacées", {{"Workdays RDC/METRE", Int64.Type}, {"Workdays DGO/RP", Int64.Type}, {"Workdays DG/DGO", Int64.Type}, {"Workdays VNT/IPU", Int64.Type}, {"Workdays IPU/OPU", Int64.Type}, {"Workdays IPU/CEPI", Int64.Type}, {"Workdays VNT/DGO", Int64.Type}, {"Workdays FGO/RP", Int64.Type}, {"DGOenPromo", type logical}})
in
  #"Type de colonne changé 3"
 
thank you for the help

@Rgieltjes_Meyn  

I had the same error "Token "," expected" when pasting the code. 

When I manually deleted the minus-sign and retyped it, the error was resolved.

 

@ImkeF Great code! Thanks so much for sharing. 

 

I got the same error, it is related with the minus sign between EndDate and StartDate.

Number.From(EndDate-StartDate)

Just delete it and write it again with your keyboard. It worked for me.

 

Thanks for the function! 🙂