Find average time between statuses

Hi Thewill4u ,

If you want to calculate the average duration of each same status like below, here's my solution.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc/RCoMwDAXQX5E8O0zShtq8CZMhuMIEn6T//xsrlhacutdwuDd322BZQ5jCC1pgZO6kI9OQVfHpgg/0ih5ie+1ExVVGO1uHeVjev8pmhYrmX5ipDG8YN9Qr5lIitbK7T2o9IlcQUgk7o7Syz4jLyuk5j3eovnXamBlW5iDGLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t, #"Change Date" = _t, Duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Status", type text}, {"Change Date", type datetime}, {"Duration", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","-",".",Replacer.ReplaceText,{"Duration"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Duration", type duration}}),
    #"Reversed Rows" = Table.ReverseRows(#"Changed Type1"),
    #"Added Index" = Table.AddIndexColumn(#"Reversed Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Min", each if[Index]=0 then 0 else List.Max(Table.SelectRows(#"Added Index",(x)=> x[Index]<[Index]and x[Status]<>[Status])[Index])+1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Max", each if List.Min(Table.SelectRows(#"Added Index",(x)=> x[Index]>[Index]and x[Status]<>[Status])[Index])-1=null then List.Max(#"Added Custom"[Index]) else List.Min(Table.SelectRows(#"Added Index",(x)=> x[Index]>[Index]and x[Status]<>[Status])[Index])-1),
    #"Added Custom3" = Table.AddColumn(#"Added Custom1", "Custom", each if[Index]>=[Min]and [Index]<=[Max]then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom3", {"Min", "Max"}, {{"Table", each _, type table [Status=nullable text, Change Date=nullable datetime, Duration=nullable duration, Index=number, Min=number, Max=number, Custom=number]}}),
    #"Added Custom4" = Table.AddColumn(#"Grouped Rows", "Average", each List.Average([Table][Duration])),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom4", "Table", {"Status", "Change Date", "Duration"}, {"Table.Status", "Table.Change Date", "Table.Duration"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Max", "Min"}),
    #"Reversed Rows1" = Table.ReverseRows(#"Removed Columns")
in
    #"Reversed Rows1"

I attach my sample below for 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.