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 o...
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
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