Power Query M code - find number of working days between 2 dates

[CloudMonkey]

Hello,

Please can you tell me how to find the number of working days between 2 dates using Power Query / M code (I have a table with all public holidays listed for my country). My current M code formula is:

NumberOfDays: [EndDate]-[StartDate]

Thanks,

CM

[v-jiascu-msft]

Hi @CloudMonkey,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[fhill]

I created your Holidays Table with a list of Holidays with a Date column of each Holiday.

1. If you don't have on already add an Index Column to your StartDate/EndDate data.  This will be used in the last step to re-group our data together.

2:  Here's M code to create a LIST of dates between each date.  This is neccessary to compare each date to the Holidays / Days of week.  After creating the LISTs, expand the column.   ** Ignore that the Index column is missing **

{ Number.From([StartDate])..Number.From([EndDate]) }

3:  Here's how the data looks with the expanded Index and DatesBetween

4:  Here's the code to create a 'IsHolidy' custom column producing a 0 if DatesBetween mathces a Date in the Holiday Table, or a Sunday or a Saturday.  1's populate for every non-holiday M-F.

=
if
List.Contains ( Table.Column(Holidays, "Date") , List.Select ( [DatesBetween]) )
or Date.DayOfWeek ( [DatesBetween] ) = 0
or Date.DayOfWeek ( [DatesBetween] ) = 6
then 0
else 1
)

5:  Now you can group the data by Index and SUM IsHoliday to determine the number of work days between each date.

[MarcelBeug · Accepted solution]

@fhill Also a nice solution, but I think the List.Generate was a mistake?

Additionally your solution will be much faster if the Holiday table is buffered.

This is what I made of your solution:

let
    BufferedHolidays = Table.Buffer(Holidays),
    Source = Data,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "DatesBetween", each { Number.From([StartDate])..Number.From([EndDate]) }),
    #"Expanded DatesBetween" = Table.ExpandListColumn(#"Added Custom", "DatesBetween"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"DatesBetween", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "IsHoliday", 
        each if List.Contains ( Table.Column(BufferedHolidays, "Date") ,  [DatesBetween] ) 
        or Date.DayOfWeek ( [DatesBetween] ) = 0
        or Date.DayOfWeek ( [DatesBetween] ) = 6
        then 0 else 1),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"StartDate", "EndDate", "Index", "Excel networkdays"}, {{"Networkdays FHill", each List.Sum([IsHoliday]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"})
in
    #"Removed Columns"

An even faster solution is to translate the function logic from my previous post, into a query, so the merges will not be done with individual nested tables, but with the entire table at once.

A drawback is that the query code becomes somewhat more complex.

let
    Weekend = #table(type table[Weekday = Int64.Type],{{0},{6}}),
    Source = Data,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Dates", each Table.FromColumns({List.Dates([StartDate],1+Duration.Days([EndDate]-[StartDate]),#duration(1,0,0,0))}, type table[Dates = date])),
    #"Expanded Dates" = Table.ExpandTableColumn(#"Added Custom", "Dates", {"Dates"}),
    #"Inserted Day of Week" = Table.AddColumn(#"Expanded Dates", "Day of Week", each Date.DayOfWeek([Dates]), type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Day of Week"},Weekend,{"Weekday"},"Inserted Day of Week",JoinKind.LeftAnti),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Dates"},Holidays,{"Date"},"Holidays",JoinKind.LeftAnti),
    #"Grouped Rows" = Table.Group(#"Merged Queries1", {"Index"}, {{"PQ Networkdays", each Table.RowCount(_), type number}}),
    #"Merged Queries2" = Table.NestedJoin(#"Added Index",{"Index"},#"Grouped Rows",{"Index"},"Grouped Rows",JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries2", "Grouped Rows", {"PQ Networkdays"}, {"PQ Networkdays"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Grouped Rows",null,0,Replacer.ReplaceValue,{"PQ Networkdays"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

[MarcelBeug]

You can name this function NetWorkDays and add a column to your table, invoking this custom function.

The results are equal to the output from Excel function NETWORKDAYS.

(StartDate as date, EndDate as date, HolidayList as list) as number =>
let
    Weekend = #table(type table[Weekday = Int64.Type],{{0},{6}}),
    Holidays = Table.FromColumns({HolidayList},type table[Weekday = date]),
    Dates = Table.FromColumns({List.Dates(StartDate,1+Duration.Days(EndDate-StartDate),#duration(1,0,0,0))}, type table[Dates = date]),
    #"Inserted Day of Week" = Table.AddColumn(Dates, "Day of Week", each Date.DayOfWeek([Dates]), type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Day of Week"},Weekend,{"Weekday"},"Inserted Day of Week",JoinKind.LeftAnti),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Dates"},Holidays,{"Weekday"},"Holidays",JoinKind.LeftAnti),
    NetWorkdays = Table.RowCount(#"Merged Queries1")
in
    NetWorkdays

[diegomsg]

Worked very well. Thanks.

Great solution!

[Anonymous]

Hi Marcel, 
Thank you for this solution. I created this function and imported the list of holidays as a Table. However when i invoke this function in my dataset to add a new column it doesn't allow me to choose the list of holidays from the holiday table. The dropdown to select holiday table is disabled. Can you please help. 

[scchuck]

Thanks for providing this solution, it works well for me. My requirements need to return a negative number if StartDate>EndDate, so I used an if statement to test for that and one call of the function for each condition.