Forum Discussion
How to calculate time difference between concecutive rows efficiently
I'm using Power Query Online and I have about 60000 rows of data with columns session_user_email and session_start_time.
I need to calculate the time difference between concecutive rows for the same email between start_times. If the emails are different, value should be null. Data is already sorted.
I'm using the Index method but it is slow. This is my code:
#"Added index" = Table.AddIndexColumn(#"Renamed columns", "Index", 0, 1, Int64.Type),
#"Added custom" =
Table.AddColumn(#"Added index", "session_end_time", each try
if
#"Added index"{[Index]}[session_user_email] =
#"Added index"{[Index] + 1}[session_user_email] then
#"Added index"{[Index] + 1}[session_start_time]
else
null
otherwise
null),How can I make this more efficient?
Anonymous
end_time = (tbl as table) as table => Table.FromColumns( Table.ToColumns(tbl) & {List.RemoveFirstN(tbl[session_start_time], 1) & {null}}, Table.ColumnNames(tbl) & {"session_end_time"} ), g = Table.Group(#"Renamed columns", "session_user_email", {"upd", (x) => end_time(Table.Sort(x, "session_start_time"))}), expand = Table.ExpandTableColumn(g, "upd", {"session_start_time", "session_end_time"})
3 Replies
Anonymous
end_time = (tbl as table) as table => Table.FromColumns( Table.ToColumns(tbl) & {List.RemoveFirstN(tbl[session_start_time], 1) & {null}}, Table.ColumnNames(tbl) & {"session_end_time"} ), g = Table.Group(#"Renamed columns", "session_user_email", {"upd", (x) => end_time(Table.Sort(x, "session_start_time"))}), expand = Table.ExpandTableColumn(g, "upd", {"session_start_time", "session_end_time"})Slap a Table.Buffer around your code.
