Anonymous

Not applicable

8 years ago

Solved

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

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,

Need Help

MarcelBeug

8 years ago

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"

7 Replies

MarcelBeug
Community Champion
8 years ago
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
- scchuck
  Regular Visitor
  6 years ago
  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.
- Anonymous
  Not applicable
  6 years ago
  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.
- diegomsg
  Frequent Visitor
  3 years ago
  Worked very well. Thanks.
  Great solution!

fhill

Resident Rockstar

8 years ago

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

Community Champion

8 years ago

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"

v-jiascu-msft
Microsoft Employee
8 years ago
Hi Anonymous,

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

Forum Discussion

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

7 Replies

Related content

Date.AddDays not working with negative numbers

Number of working days

Column sums by text work order numbers

Number of people working per day

Table.FuzzyNestedJoin: Ignore.Space = true not working with numbers