cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Partisan
Post Partisan

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,

 

CM

1 ACCEPTED SOLUTION
Community Champion
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"
Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User I
Super User I

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.

 

Capture2.PNG

 

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]) }

 

Capture.PNG

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

Capture3.PNG

 

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
)

Capture4.PNG

 

 

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

Capture5.PNG

 



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




Community Champion
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"
Specializing in Power Query Formula Language (M)

View solution in original post

Community Champion
Community Champion

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
Specializing in Power Query Formula Language (M)

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. 

richabathija_0-1593618798258.png

 

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors