Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

How to create a Conditional Column, in Power Query Editor from Subtracting Timestamps?

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZJLDgMhDEOvUrGu1MQJELjKqPe/RvmoMwGWWH7YCVxX4PAOTESWNXFsB+IPlQ8I8mKuVDYpV9bwfV8BDiQ7XEJdwi0RVcoDlBuMSnK4YtqkXGkm6g0mxNGrPImxSl4loOoE4w0a67hFloHSKkGq0gDTDZZs/XqQd432eMam2PfVwfyABhwulFVqi45xgObAxJtLqmyJoKo2wOKqIm2u9o6jhNsqKuaMTC5S7LBJ2qT2tjOS2ZF5D7D5d5zE+v87DD/madsz25iYn4fFkbZl9hcfkiw15mZZfdty2KCr1Nu2zO8P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, BATCH_ID = _t, START_DATE = _t, STOP_DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"BATCH_ID", Int64.Type}, {"START_DATE", type datetime}, {"STOP_DATE", type datetime}},"en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each [STOP_DATE]-[START_DATE],type duration),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Idle time", each if [ID]=1 then null else [START_DATE]-#"Added Custom"{[ID]-2}[STOP_DATE],type duration)
in
    #"Added Custom1"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".