NETWORKDAYS in Hours

Hi Anonymous ,

According to your description, here's my solution. Copy-paste the below code to a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtM31DcyMDJWMLCwMjAAIiUdhJihEVQsVgeXSiOsKo2xqDQl2kwLdJWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Received" = _t, #"Order Delivered" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Received", type datetime}, {"Order Delivered", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each {Number.From(DateTime.Date([Order Received]))+1..Number.From(DateTime.Date([Order Delivered]))-1}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1", type date}}),
    #"Transform"=Table.ReplaceValue(#"Changed Type1",each[Custom.1],each try if List.Contains(#"Date"[Date],[Custom.1]) then [Custom.1]else if Date.DayOfWeek([Custom.1],Day.Monday)<5 then null else[Custom.1]otherwise null,Replacer.ReplaceValue,{"Custom.1"}),
    #"Grouped Rows" = Table.Group(Transform, {"Order Received", "Order Delivered"}, {{"Count", each List.NonNullCount(_[Custom.1]), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hours", each Duration.TotalHours((if Date.DayOfWeek([Order Delivered],Day.Monday)>=5then DateTime.From(Date.AddDays(DateTime.Date([Order Delivered]),1)) else [Order Delivered])-(if Date.DayOfWeek([Order Received],Day.Monday)>=5then DateTime.From(Date.AddDays(DateTime.Date([Order Received]),1)) else [Order Received]))-[Count]*24)
in
    #"Added Custom"

Date table is a list of dates:

Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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