Forum Discussion

pfz's avatar
pfz
New Member
5 years ago
Solved

Date difference between consecutives events with different values

Hi, I have the following table and I want to calculate the date difference between the time stamps whenever the Event value changes from the value of 2. So, for example, in relation to the table: -...
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7BCcAwDAPAVYrfgVh2XUpWCdl/jSr0YWj98EuH5DkFqtLEeIquVzc1HPARPpistgWY4i8ihdUddwpn6h+xC1KcxQoFUkSxQvH+sR4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EventValue = _t, TimeStamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"EventValue", Int64.Type}, {"TimeStamp", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = let pos = List.PositionOf(#"Added Index"[EventValue],2,Occurrence.All) in Table.Group(#"Added Index", "Index", {"ar", each _}, 0, (x,y)=>Number.From(List.Contains(pos,y-1))),
    Elapse = Table.FromRecords(List.Transform(#"Grouped Rows"[ar], each let first=Table.First(_), last=Table.Last(_) in [Start=first[ID], End=last[ID], Elapse=last[TimeStamp]-first[TimeStamp]]))
in
    Elapse

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7BCcAwDAPAVYrfgVh2XUpWCdl/jSr0YWj98EuH5DkFqtLEeIquVzc1HPARPpistgWY4i8ihdUddwpn6h+xC1KcxQoFUkSxQvH+sR4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EventValue = _t, TimeStamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"EventValue", Int64.Type}, {"TimeStamp", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = let pos = List.PositionOf(#"Added Index"[EventValue],2,Occurrence.All) in Table.Group(#"Added Index", "Index", {"ar", each _}, 0, (x,y)=>Number.From(List.Contains(pos,y-1))),
    Elapse = Table.FromRecords(List.Transform(#"Grouped Rows"[ar], each let first=Table.First(_), last=Table.Last(_) in [Start=first[ID], End=last[ID], Elapse=last[TimeStamp]-first[TimeStamp]]))
in
    Elapse