Hay un montón de formas de abordar esto, pero un método que he utilizado varias veces es generar columnas de índice de desplazamiento y hacer una autocombinión que coincida con las dos columnas de índice para obtener la fila anterior. Es un poco extraño pero más eficiente computacionalmente que cualquier otro método que se me haya ocurrido.
Aquí hay una variación ligeramente diferente similar a las que vinculé:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdHREYMgEATQVhy+jdweBxz0kAoc+28jCkTNoOaPjzcsu8yzSZbZMjEN0ExiRvN2nvACdD0Pg0RNZhlnIxYFYoNIHfQksUBvfXOSWe9dsIgN+gy6gjUZR3LMhA1qhVxgYp2kSGe1QqbsegiHeNflGv50eXBqHeqK7LLICYYKmTDF1ibsb/Shl25t+Njl6+D/dKmQmZpjaR9NWc7BUi9Uv899rE29C8xmWT4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, SerialId = _t, GenHours = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"SerialId", type text}, {"GenHours", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index0", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index0"}, #"Added Index1", {"Index1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"SerialId", "GenHours"}, {"SerialId.1", "GenHours.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Diff", each if [SerialId] = [SerialId.1] then [GenHours] - [GenHours.1] else null, type number),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index0", "Index1", "SerialId.1", "GenHours.1"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"SerialId", Order.Ascending}, {"DateTime", Order.Ascending}})
in
#"Sorted Rows"
Intente pegar esto en su ventana del Editor avanzado y repasó los pasos de uno en uno.
Editar:
Como sugiere @speedramps, podemos hacer la combinación en varias columnas para eliminar la comprobación [SerialId] = [SerialId.1].
Aquí está la consulta modificada:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdHREYMgEATQVhy+jdweBxz0kAoc+28jCkTNoOaPjzcsu8yzSZbZMjEN0ExiRvN2nvACdD0Pg0RNZhlnIxYFYoNIHfQksUBvfXOSWe9dsIgN+gy6gjUZR3LMhA1qhVxgYp2kSGe1QqbsegiHeNflGv50eXBqHeqK7LLICYYKmTDF1ibsb/Shl25t+Njl6+D/dKmQmZpjaR9NWc7BUi9Uv899rE29C8xmWT4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, SerialId = _t, GenHours = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"SerialId", type text}, {"GenHours", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index0", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index0", "SerialId"}, #"Added Index1", {"Index1", "SerialId"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"SerialId", "GenHours"}, {"SerialId.1", "GenHours.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Diff", each [GenHours] - [GenHours.1], type number),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index0", "Index1", "SerialId.1", "GenHours.1"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"SerialId", Order.Ascending}, {"DateTime", Order.Ascending}})
in
#"Sorted Rows"
