How to calculate duration between connect time and disconnect time when they are in different rows

[Pelas68]

Hi,

Could someone to help me. I have table of multible logins per users with connect timestamp and disconnect timestamp. The event_data (colum 3) is allways unique with one connect and disconnect session pair. I would like to calculate duration between one connect and disconnect session pair per user. I know basically how to calculate duration between two times but this time the timestamps are in different rows so I am a little confused how to solve this issue...

[KeyurPatel14 · Accepted solution]

Hi @Pelas68 ,

Hope you are doing well.
Please try the below M Code and let me know if ther eis any queries:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7Py0tNLlHSUVIwMtc3NNQ3MjAyUjAysjI1tzIxBQobAgGQCjVUitXBp9zCytgIKGwEBCDlRmDlLpnFyTh1WFoZmGBYgFuHsZWBgZUBmh2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [event = _t, time = _t, data = _t, user = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"time", type datetimezone}}, "en-IN"),
#"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.From([time])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "time1", each try #"Added Index" [time] {[Index] + 1} otherwise null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"event", "time", "time1", "data", "user"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([time1] <> null)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Custom", each [time1] - [time])
in
#"Added Custom2"

I hope this will solve your problem and if this helps then give it a kudos and mark it as a solution.
Thank you.

Below is the image of expected result:

[KeyurPatel14 · Accepted solution]

If you want Disconnect in your event column then kindly paste below M Code in your Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7Py0tNLlHSUVIwMtc3NNQ3MjAyUjAysjI1tzIxBQobAgGQCjVUitXBp9zCytgIKGwEBCDlRmDlLpnFyTh1WFoZmGBYgFuHsZWBgZUBmh2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [event = _t, time = _t, data = _t, user = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"time", type datetimezone}}, "en-IN"),
#"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.From([time])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "time1", each try #"Added Index" [time] {[Index] + 1} otherwise null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"event", "time", "time1", "data", "user"}),
#"Added Custom2" = Table.AddColumn(#"Reordered Columns", "Time Difference", each [time1] - [time])
in
#"Added Custom2"

Hope this will solve your problem and if it helps then please give it a kudos and accept it as a solution.
Thank you.

[KeyurPatel14 · Accepted solution]

Hi @Pelas68 ,

Hope you are doing well.
Please try the below M Code and let me know if ther eis any queries:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7Py0tNLlHSUVIwMtc3NNQ3MjAyUjAysjI1tzIxBQobAgGQCjVUitXBp9zCytgIKGwEBCDlRmDlLpnFyTh1WFoZmGBYgFuHsZWBgZUBmh2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [event = _t, time = _t, data = _t, user = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"time", type datetimezone}}, "en-IN"),
#"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.From([time])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "time1", each try #"Added Index" [time] {[Index] + 1} otherwise null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"event", "time", "time1", "data", "user"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([time1] <> null)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Custom", each [time1] - [time])
in
#"Added Custom2"

I hope this will solve your problem and if this helps then give it a kudos and mark it as a solution.
Thank you.

Below is the image of expected result: