Forum Discussion
Power Query M code - find number of working days between 2 dates
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"
Community ChampionYou 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
NetWorkdaysThanks 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.
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.Worked very well. Thanks.
Great solution!
