Running Difference between rows

[umairarshad]

hello all,

For the following data, I want to have running difference between consective rows.

Please help me out in this regard.

like

83-5780

120-83

16989-120

629-16989

and so on.

Regards,

[umairarshad]

Great. It worked. many thanks

[Ahmedx]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI1tzBQitWBcCyM4UxDI4SwoZmlhSWY5wTkmRkh2EYGZkZwjoUJnGlsaqEUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hours = _t, #"Running Hours" = _t]),
    Types = Table.TransformColumnTypes(Source,{{"Running Hours", Int64.Type}}),
    f= (x)=> [
      nametblcolumn = Table.ColumnNames(x),
    a= Table.ToColumns(x),
    b = a&{{null}&List.RemoveLastN(a{1},1)},
    c = Table.FromColumns(b, nametblcolumn&{"NewColumn"}),
    d= Table.RemoveColumns( Table.ReplaceValue(c,each [Running Hours],each [Running Hours]-[NewColumn],Replacer.ReplaceValue,{"Running Hours", "NewColumn"}),{"NewColumn"})][d],

    final = f(Types)
in
    final

[BA_Pete · Accepted solution]

Hi @umairarshad ,

Try this example code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjW3MFCK1YlWsjAGU4ZGEK6hmaWFJZhlZgShjQzMjCAqTcCUsamFUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Running Hours" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Running Hours", Int64.Type}}),

// Relevant steps from here ===>
    addIndex0 = Table.AddIndexColumn(chgTypes, "Index", 0, 1, Int64.Type),
    addRunningDiff = Table.AddColumn(addIndex0, "RunningDiff", each try [Running Hours] - addIndex0[Running Hours]{[Index] - 1} otherwise null)
in
    addRunningDiff

Summary:

First, you need to sort your table so the numbers are in the order you need them to be for subtraction.

addIndex0 = Add an index column starting from zero. This allows us to reference this as a proxy for row number.

addRunningDiff = Deduct prior row value using previous step and [Index] value minus 1 from each row value.

Example output:

Pete