Date.Networkdays.Intl in Power Query Language M

[Vijay_A_Verma]

Use Case - Excel provides two very useful functions - NETWORKDAYS and NETWORKDAYS.INTL which are not found in Power Query language M.

The key difference between these functions is that in NETWORKDAYS, weekends are fixed to Saturday and Sunday whereas in NETWORKDAYS.INTL, you can control which days should be treated as weeknds. For example, many countries have one day weekend say Sunday. Then these countries can't use NETWORKDAYS, they will have to use NETWORKDAYS.INTL. In Gulf countries, Friday and Saturday are treated as weekends making NETWORKDAYS worthless. They will have to use NETWORKDAYS.INTL. In one industial town, Thursday is the day when power cut is imposed, hence they follow Thursday as weekend (6 days a week working).

Following is the syntax for NETWORKDAYS.INTL

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

There are two ways to control weekend parameter

1. Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Using 1111111 will always return 0.

For example, 0000011 would result in a weekend that is Saturday and Sunday.

2. You can pass numbers also as weekend control parameter as below

BiAccountant provides a function to calculate NETWORKDAYS in Power Query language M @ 
Date.Networkdays function for Power Query and Power BI

This article is inspired by the above and takes it to one level up by providing function for NETWORKDAYS.INTL in Power Query language M.

Note - Some of the logic is taken from above while building this function.

Solution - StartDate and EndDate are mandatory parameters. Weekend list and Holidays list are optional parameters. Use below function code for Date.Networkdays.Intl function in Power Query. Weekend can be passed as "1" to "7" Or "11" to "17" or as 7 digits string such as "0000110" (This has been described above). Holidays can be passed as a list of dates. Hence, note, Holidays is a list not a string. You can see the examples given in the attached Excel at the end.

(StartDate, EndDate, optional Weekend, optional Holidays)=> 
let
    //if a date is blank, make it equal to 30-Dec-1899
    FromDate = if StartDate = null then #date(1899,12,30) else StartDate,
    ToDate = if EndDate = null then #date(1899,12,30) else EndDate,
    //StartDate should be lesser than or equal to EndDate. If it is more than EndDate, then we will multiply the final result with negative sign. Let's store this sign
    Sign = if ToDate<FromDate then -1 else 1,
    Start_Date = List.Min({FromDate, ToDate}),
    End_Date = List.Max({FromDate, ToDate}),
    //Prepare a list of dates from Start date to End date and remove Holidays from there
    ListOfAllDates = List.Difference(List.Dates(Start_Date,Duration.Days(End_Date-Start_Date)+1,#duration(1,0,0,0)),Holidays),
    //Adjust Weekend Parameter
    SetWeekend = if Weekend ="" or Weekend = null then "1" else Text.From(Weekend),
    //1 to 7 and 11 to 17 are parameters for weekends - https://support.microsoft.com/en-us/office/networkdays-intl-function-a9b26239-4f20-46a1-9ab8-4e925bfd5e28
    WeekendParamList = {"1".."7"} & {"11","12","13","14","15","16","17"},
    WeekendLookupList = {"0000011","1000001","1100000","0110000","0011000","0001100","0000110","0000001","1000000","0100000","0010000","0001000","0000100","0000010"},
    WeekendString = if Text.Length(SetWeekend)=7 then SetWeekend else WeekendLookupList{List.PositionOf(WeekendParamList,SetWeekend)},
    //Let's generate a list which has the position of weekends. Hence for 1000011 will generate, {1,6,7}.
    WeekendList = List.RemoveMatchingItems(List.Transform(List.Positions(Text.ToList(WeekendString)), (i)=>(i+1)*Number.From(Text.ToList(WeekendString){i})),{0}),
    //Let's remove the dates which match the weekend criteria and take the count multiplied by Sign
    Networkdays = Sign * List.Count(List.Select(ListOfAllDates,(i)=>not List.Contains(WeekendList,Date.DayOfWeek(i,1)+1)))
in
    Networkdays

Below is the result for some test cases.

Excel file containing above testcase as well as code can be downloaded from https://1drv.ms/x/s!Akd5y6ruJhvhuXn1RNOEktlvHlri?e=ig5Nc2