Create Custom Column that finds value based off next instance of matching criteria

[Desokolowski]

I have a Data Set of when an an Agent Went into a Specific State.
I want to Get the Duration of those Specific States.

I have been approaching this issue by trying to get an End Time to calculate the Duration. 

I can assume that the End Time is the next Start time for that Agent.
I have been looking to create a custom column that searches for the Next chronological instance that a Persons Name occurs and gets that Start Time to be the End Time in power query.


Example of Data
This is what I have

This is what I am after

[dufoq3]

Result

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca)
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevtValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type text})
        in
            d,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9c3MjAyVjCwMrBU0lHyys/IA1L+aWk5mXmpSrE6qGqMzRFqHMsSM3MSk3IwVZkiqypPrERTYGhlYETYGEMrQwOQqkSgM3A5yNDKyISQow2tjA2IsQ3iNYhtuFQZWRlYEHKTkZWhCTEmGRkCpUMyc/EYZGIGV4LbHBNLQi4ygYQ3PrtMIN7HsCsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Time" = _t, Agent = _t, state = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start Time", type datetime}, {"Agent", type text}, {"state", type text}}),
    // Added [End Time]
    GroupedRows = Table.Group(ChangedType, {"Agent"}, {{"All", each fnShift(_, "Start Time", -1, "End Time", type datetime) , type table }}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Ad_Duration = Table.AddColumn(CombinedAll, "Duration", each [End Time] - [Start Time], type duration)
in
    Ad_Duration

[Anonymous · Accepted solution]

Hi @Desokolowski 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9c3MjAyVjCwMrBU0lHyys/IA1L+aWk5mXmpSrE6qGqMzRFqHMsSczITk3IwVZkiqypPrERTYGhlYETYGEMrQwOQqkSgM3A5yNDKyISQow2tjA2IsQ3iNYhtuFQZWRlYEHKTkZWhCTEmGRkCpUMyc/EYZGIGV4LbHBNLQi4ygYQ3PrtMIN7HsCsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Time" = _t, Agent = _t, State = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Time", type datetime}, {"Agent", type text}, {"State", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Agent"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Start Time", "State", "Index"}, {"Start Time", "State", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "EndTime", each List.Min(Table.SelectRows(#"Expanded Data",(x)=>x[Agent]=[Agent] and x[Index]=[Index]+1)[Start Time])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Duration", each [EndTime]-[Start Time]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([EndTime] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Start Time", Order.Ascending}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Start Time", type datetime}, {"EndTime", type datetime}, {"Index", Int64.Type}, {"Duration", type duration}})
in
    #"Changed Type1"

Output

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.

[spinfuzer]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9c3MjAyVjCwMrBU0lHyys/IA1L+aWk5mXmpSrE6qGqMzRFqHMsSM3MSk3IwVZkiqypPrERTYGhlYETYGEMrQwOQqkSgM3A5yNDKyISQow2tjA2IsQ3iNYhtuFQZWRlYEHKTkZWhCTEmGRkCpUMyc/EYZGIGV4LbHBNLQi4ygYQ3PrtMIN7HsCsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Time" = _t, Agent = _t, state = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Time", type datetime}, {"Agent", type text}, {"state", type text}}),
    getEndTime =
        (tbl as table) =>
        let 
            row = List.Buffer(Table.ToRecords(tbl))
        in
            List.Generate( () =>
            [n = 0],
            each [n] < List.Count(row),
            each [n = [n] + 1],
            each row{[n]} & 
            [
                End Time = try row{[n]+1}[Start Time] otherwise null, 
                Duration = Duration.From(Duration.TotalDays(#"End Time" - row{[n]}[Start Time]))
            ]
            ),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Agent"}, {"end time", each getEndTime(_)})
in 
    #"Grouped Rows"