Date difference between consecutives events with different values

[pfz]

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)

ID	EventValue	TimeStamp
100	2	01/06/2021 13:53:00
101	1	01/06/2021 13:55:00
102	2	01/06/2021 13:58:00
103	3	01/06/2021 15:00:00
104	1	01/06/2021 15:01:00
105	2	01/06/2021 15:05:00

[CNENFRNL · Accepted solution]

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