Calculate net working days and total days between two dates

[joshua1990]

Hi experts!

I have a table that has a date column. Now I would like to add two additional columns.

The first one shows me the number of working days (Mo-Fr) between the Date and Today.

The second shows me the number of all days (Mo-So) between the Date and Today.

How is this possible using Power Query?

[Anonymous · Accepted solution]

Hi @joshua1990 ,

Here is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcfJCQAgDATAXvJ2wV3vWiT9t6EPIcJ8Zm9TFkEU8/TSwBkbUI1NsMcWOGLMkL5e7auwzP0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Today", each DateTime.LocalNow()),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Today", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Days", each Duration.Days([Today]-[Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Dates([Date],[Days],#duration(1, 0, 0, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Date.DayOfWeek([Custom],Day.Sunday)),
    #"Grouped Rows" = Table.Group(#"Added Custom3", {"Date"}, {{"Count", each _, type table [Date=nullable date, Today=nullable date, Days=number, Custom=date]}}),
    #"Added Custom4" = Table.AddColumn(#"Grouped Rows", "Mon-Fri", each Table.RowCount(Table.SelectRows([Count], each [Custom.1]<=5 and [Custom.1]>=0))),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Total Days", each Duration.Days(DateTime.Date( DateTime.LocalNow())- [Date])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Count"})
in
    #"Removed Columns"

Output:

You could check the pbix file attached.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

this should get you going 

Date.DayOfWeek - PowerQuery M | Microsoft Docs

You will want to be very specific what you mean by "Today" - including timezones etc.