Forum Discussion
Substract break time and shift change from durations
1. Since 4th entry finished at 07:34 and break started at 07:30, hence total break time will be = 43+60+4 = 107 minutes = 1.78 hours
Also total minutes for this entry = start of 07:32 to end of 07:34 = 12*60+2 = 722 minutes = 12.03 hours
Hence net duration = 12.03-1.778 = 10.25 hours
2. For second entry = Total duration is 309 minutes which is 5.15 hours not 5.2 hours
Below are correct results
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYxJCsAgEAS/Ip4Fe2YUcW55gJC7+P9vBHGJgVyruqtWGzxnz2A2UQkKmKtYd2AijaHzu9jmzkNWwRT9IBiYVfLsjP0SpEg/+6Qkn/4rhPc+eto0rHx7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start time" = _t, #"Finish time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start time", type datetime}, {"Finish time", type datetime}}), ListOfBreaksInMinutes = List.Times(#time(0,0,0),60,#duration(0,0,1,0))&List.Times(#time(7,30,0),45,#duration(0,0,1,0))&List.Times(#time(12,0,0),60,#duration(0,0,1,0))&List.Times(#time(19,30,0),45,#duration(0,0,1,0)), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.Round(List.Count(List.Difference(List.Transform(List.DateTimes([Start time],Duration.TotalMinutes([Finish time]-[Start time]),#duration(0,0,1,0)),each Time.From(_)),ListOfBreaksInMinutes))/60,2)) in #"Added Custom"Better to create 2 columns Start_Time and Finish_Time were you should set seconds to 0. Then you should refer to these 2 fields in the formula. In the end, delete these 2 columns.
You right, below is the revised table.
1. Since 4th entry finished at 07:34 and break started at 07:30, hence total break time will be = 43+60+4 = 107 minutes = 1.78 hours
Also total minutes for this entry = start of 07:32 to end of 07:34 = 12*60+2 = 722 minutes = 12.03 hours
Hence net duration = 12.03-1.778 = 10.25 hours
2. For second entry = Total duration is 309 minutes which is 5.15 hours not 5.2 hours
Below are correct results
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYxJCsAgEAS/Ip4Fe2YUcW55gJC7+P9vBHGJgVyruqtWGzxnz2A2UQkKmKtYd2AijaHzu9jmzkNWwRT9IBiYVfLsjP0SpEg/+6Qkn/4rhPc+eto0rHx7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start time" = _t, #"Finish time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start time", type datetime}, {"Finish time", type datetime}}),
ListOfBreaksInMinutes = List.Times(#time(0,0,0),60,#duration(0,0,1,0))&List.Times(#time(7,30,0),45,#duration(0,0,1,0))&List.Times(#time(12,0,0),60,#duration(0,0,1,0))&List.Times(#time(19,30,0),45,#duration(0,0,1,0)),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.Round(List.Count(List.Difference(List.Transform(List.DateTimes([Start time],Duration.TotalMinutes([Finish time]-[Start time]),#duration(0,0,1,0)),each Time.From(_)),ListOfBreaksInMinutes))/60,2))
in
#"Added Custom"
That works wonder! However, i missed out to mention earlier that datetime on my data are down to seconds increment, whereas on your query it is rounded to the nearest minute, i modify your query to below. See the List.DateTimes part where i restate the [Start time] to zero the seconds, I am not sure but it seems inefficient and I am afraid it will take the query longer when refreshed. Any suggestion to simplify it?
Custom1 = Table.AddColumn(#"Reordered Columns", "Nett operation lead time", each Number.Round(List.Count(List.Difference(List.Transform(List.DateTimes(#datetime(Date.Year([Start time]),Date.Month([Start time]),Date.Day([Start time]),Time.Hour([Start time]),Time.Minute([Start time]),0),Duration.TotalMinutes([Finish time]-[Start time]),#duration(0,0,1,0)),each Time.From(_)),ListOfBreaksInMinutes))/60,1))Better to create 2 columns Start_Time and Finish_Time were you should set seconds to 0. Then you should refer to these 2 fields in the formula. In the end, delete these 2 columns.
Got it, thanks a lot!