Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a table of timestamps that are created whenever an employee starts working, goes on a break etc. All the times are just start times and I a trying to get the "end time" on the same row based on next start time of the employee.
Here is a sample of the data:
Here is what I have come up with. I need to have "null" as the end_time for the last row of User 1 because that user does not have a next timestamp in a similar fashion as User 2 has null as the last value.
The end goal is to calculate the durations between the timestamps on the same row.
How can I insert "null" when the cr554_name changes?
Solved! Go to Solution.
Hello, @Anonymous group by user and for each table: add index, add column end_time with try ... otherwise null.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjVU0lEy0TfWNzIwMlYwNLAyMAAKlOcXZSvF6mBIG0Kkk4pSE7HKG+HXboyh3Qi/7UbYTIdqjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [user = _t, start_time = _t, #"type" = _t]),
user_groups =
Table.Group(
Source, {"user"},
{{"all",
(x) =>
let
add_index = Table.AddIndexColumn(x, "Index", 0, 1),
end_time = Table.AddColumn(add_index, "end_time", (w) => try add_index[start_time]{w[Index] + 1} otherwise null)
in end_time
}}),
z = Table.Combine(user_groups[all])
in
z
Hello, @Anonymous group by user and for each table: add index, add column end_time with try ... otherwise null.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjVU0lEy0TfWNzIwMlYwNLAyMAAKlOcXZSvF6mBIG0Kkk4pSE7HKG+HXboyh3Qi/7UbYTIdqjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [user = _t, start_time = _t, #"type" = _t]),
user_groups =
Table.Group(
Source, {"user"},
{{"all",
(x) =>
let
add_index = Table.AddIndexColumn(x, "Index", 0, 1),
end_time = Table.AddColumn(add_index, "end_time", (w) => try add_index[start_time]{w[Index] + 1} otherwise null)
in end_time
}}),
z = Table.Combine(user_groups[all])
in
z