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:

-the difference between event ID 101 and 102  (equals to 3 minutes);

-the difference between event ID 103 and 105 (equals to 5 minutes)

 

IDEventValueTimeStamp
100201/06/2021 13:53:00
101101/06/2021 13:55:00
102201/06/2021 13:58:00
103301/06/2021 15:00:00
104101/06/2021 15:01:00
105201/06/2021 15:05:00
  • 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

1 Reply

  • 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