Forum Discussion

Desokolowski's avatar
Desokolowski
New Member
2 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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