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"
Resident RockstarI 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
Community Champion
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"