Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I would like to calculate NETWORKDAYS between 2 date/times in hours (not days) in power query (not DAX), excluding weekeneds. Ideally, exalso excluding specific dates from another table (Bank Holidays).
Is there any way to do that? Thanks in advance! 🙂
Solved! Go to Solution.
Hi @DimAt ,
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.
Hi @DimAt ,
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.
Hello v-yanjiang-msft,
Please find an example below (Dates in D/M/YYYY format):
Case 1:
Order Received: 1/6/2023 08:00:00
Order Delivered: 1/6/2023 12:00:00
Delivery Duration (h): 4
Explanation:
Days excluding weekends: 1/6-1/6 = 0 (no weekend)
Hours: 12-8 = 4; Minutes: 0-0 = 0
Duration (h): 0*24+4 = 4
Case 2:
Order Received: 1/6/2023 08:00:00
Order Delivered: 2/6/2023 12:00:00
Delivery Duration (h): 28
Explanation:
Days excluding weekends: 2/6-1/6 = 1 (no weekend)
Hours: 12-8 = 4; Minutes: 0-0 = 0
Duration (h): 1*24+4 = 28
Case 3:
Order Received: 1/6/2023 08:00:00
Order Delivered: 5/6/2023 12:00:00
Delivery Duration (h): 52
Explanation:
Days excluding weekends: 5/6-1/6 - 2 (Sat/Sun) = 2 (3/6/2023 Saturday, 4/6/23023: Sunday)
Hours: 12-8 = 4; Minutes: 0-0 = 0
Duration (h): 2*24+4 = 52
Bank Holidays can be in another tables with those dates being excluded from the calculation (like Saturdays/Sundays).
Can you share sample input and Expected output?
User | Count |
---|---|
97 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |