Restar fechas entre filas agregando columna

[lauraespinade]

Buenas,

Soy nueva en Power Bi y no se si es posible restar fechas entre filas de una misma columna sin hacer uso de una medida.

¿ Es posible hacerlo con alguna funcion directamente sobre la tabla de datos?

En el caso de la imagen, agregar una columna que en la segunda fila me de el valor de 0:00:51, en la tercera fila de 0:00:50

[Vijay_A_Verma · Accepted solution]

Since you have just started, I will not give the complex way but the easiest way. Which is through Index. To see the working, refer below -

Open a blank Query - Advanced Editor - Remove everything from there - Paste the below code - OK

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw0TUw0jUyMDJSMDCzMjKwMjBTitWJVnLCJmdqDpZzwZQztDKByPliyhlbGViC5fywyZkaKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Fechahora = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Fechahora", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Diff", each try [Fechahora]-#"Changed Type"[Fechahora]{[Index]-1} otherwise null)
in
    #"Added Custom"

[ArgenSuarez]

Buenas Tardes, alguien me podra ayudar para ver la solución de restar fechas de distintos eventos, es decir el tiempo que esta detenida una tarea.

Seria el valor fecha en Amarillo menos el valor en rojo.

Saludos y muchas gracias.

Los Leo

[Vijay_A_Verma · Accepted solution]

Since you have just started, I will not give the complex way but the easiest way. Which is through Index. To see the working, refer below -

Open a blank Query - Advanced Editor - Remove everything from there - Paste the below code - OK

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw0TUw0jUyMDJSMDCzMjKwMjBTitWJVnLCJmdqDpZzwZQztDKByPliyhlbGViC5fywyZkaKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Fechahora = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Fechahora", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Diff", each try [Fechahora]-#"Changed Type"[Fechahora]{[Index]-1} otherwise null)
in
    #"Added Custom"

[lauraespinade · Accepted solution]

My last doubt is if it is possible in this way to calculate the difference but filtering by an ID.

In this table I have a column with different Ids, so I want to calculate the differences between hours for each ID.

[lauraespinade]

Thank you !!!

It works well 🙂 So useful to know it