Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to insert null value based on value on another column?

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?

 

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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

1 Reply

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    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