NEW to PBI

[SHER101]

How do I edit this to not include weekends

= Table.AddColumn(#"Added Conditional Column", "SiteWalk CycleTime", each Duration.Days ([site_visit_acd] - [cpin_received_acd]))

[v-junyant-msft · Accepted solution]

Hi @SHER101 ,

Please try this:

let 
   StartDate = [site_visit_acd],
   EndDate = [cpin_received_acd],
   TotalDays = Duration.Days(EndDate - StartDate) + 1,
   RemoveWeekends = List.Numbers(0, TotalDays, 1),
   DaysList = List.Transform(RemoveWeekends, each Date.AddDays(StartDate, _)),
   WeekDays = List.Select(DaysList, each Date.DayOfWeek(_, Day.Monday) < 5)
in 
   List.Count(WeekDays)

Here is the whole M code in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtEz1DNU0oExzZVidaDiRghxIz0zhLgxQtxYzxQhboIQB0Kl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [site_visit_acd = _t, cpin_received_acd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"site_visit_acd", type date}, {"cpin_received_acd", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "SiteWalk CycleTime", each let 
   StartDate = [site_visit_acd],
   EndDate = [cpin_received_acd],
   TotalDays = Duration.Days(EndDate - StartDate) + 1,
   RemoveWeekends = List.Numbers(0, TotalDays, 1),
   DaysList = List.Transform(RemoveWeekends, each Date.AddDays(StartDate, _)),
   WeekDays = List.Select(DaysList, each Date.DayOfWeek(_, Day.Monday) < 5)
in 
   List.Count(WeekDays))
in
    #"Added Custom"

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

[lbendlin]

What is your definition of weekend?

What is your expected result when [site_visit_acd] is the same as [cpi_received_acd] ?