Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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:

Screenshot 2023-04-06 at 11.38.41.png

 

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.

Screenshot 2023-04-06 at 11.40.43.png

 

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?

 

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

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

View solution in original post

1 REPLY 1
AlienSx
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.