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.

     

spinfuzer's avatar
spinfuzer
Solution Sage
2 years ago

 

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"