Calculate leadtime from a log file

Please clarify what your interpretation of work days and business hours is.

Hi h4_dkj 

You can create a blank query and put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZFLDoMwDAWvUrFukZ8/gfgMqLuuKu5/jdrQEkg3Xjij57Hzfg8sD554uA8YEZV1pDoysdxQXSha5ExuZVjvF5r/aMsAFDdx1B3nB+QUXhquTiVbcK2Ouce39NpwuFk+VFdxRY9LesoI2nGaXGq0zC3s5159o/Wgwa4aredrWa7BvGljbqg4190jZgA9npViWXw9ZpfcMm5jTvzzEJqOm1A509DNmmJN6mm+0qmy08Yh05vkWJoaHSa47NiC8xywhsZ/lANdPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Project Status" = _t, #"Project Status Entry Timestamp" = _t, leadtime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Status", type number}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Project Status Entry Timestamp", type datetime}}, "en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Project Status Entry Timestamp", Order.Ascending}, {"Project", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Project"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Project Status", "Project Status Entry Timestamp", "leadtime", "Index"}, {"Project Status", "Project Status Entry Timestamp", "leadtime", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Count",{"Project", "Project Status", "Project Status Entry Timestamp", "Index", "leadtime"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each List.Min(Table.SelectRows(#"Reordered Columns",(x)=>x[Project]=[Project] and x[Index]=[Index]+1)[Project Status Entry Timestamp])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]<>null then Duration.Hours([Custom]-[Project Status Entry Timestamp]) else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom]<>null then Duration.Minutes([Custom]-[Project Status Entry Timestamp]) else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if [Custom]<>null then Duration.Seconds([Custom]-[Project Status Entry Timestamp]) else null),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom3", {{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}, "en-US"),{"Custom.1", "Custom.2", "Custom.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns",each [Merged],each if [Custom]<>null then [Merged] else null,Replacer.ReplaceValue,{"Merged"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Index", "leadtime", "Custom"})
in
    #"Removed Columns"

Best Regards!

Yolo Zhu

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