How do you turn an accumulating number over time into the delta per time period in M (Power Query)?

Question

Say that you would have a table that has a value of a certain account code, which is updated every three months. However, the amount is accumulating over the year, where data of 31-12 would be a full year, even though there is also data over the past three quarters. Now I would like to transform that column of values into a delta per quarter, instead of the accumulated total up until that date. So quarter two would be the value of quarter two, minus the value of quarter one. Quarter three would be the value of quarter three, minus quarter two and one etc. For the example, I would like the value of 31-12 to be 517746,90 - 387949,44 = 129797,46. This would make it possible to sum all four quarters, and get the total that you would otherwise see as the value if you only select quarter four. How would one do this in M?

Example:-

Answer 1

The way I've done something like that is to use List.Zip to create a list of {current,prev} lists and then transform that into the difference between each pair of values. Then you have to add that list as a new column.

Query fAppendDeltaColumn

(Source as table, ColumnName as text) as table =>
let
    Values = Table.Column(Source, ColumnName),
    // All but the last value of Values, prepended with 0
    Prevs = List.Combine({
        {0},
        List.FirstN(
            Values,
            List.Count(Values) - 1
        )
    }),
    Deltas = List.Transform(
        List.Zip({Values, Prevs}),
        each _{0} - _{1}
    ),
    // So now we've got a list of delta values per row. Append it to the table.
    #"Appended Delta" = fAppendColumns(Source, {"Delta"}, {Deltas}, {type number})
in
    #"Appended Delta"

Query fAppendColumns

This is something I've written and used when I want to append lists of values as new columns. I'm not convinced this is a good method. If there's a better way, apologies in advance. :-)

  (table as table, NamePerColumn as list, ValuesPerColumn as list, optional TypePerColumn as nullable list) =>
let
    #"Added Index for fAppendColumns" = Table.AddIndexColumn(table, "IndexForAppendColumns", 0, 1),
    #"Added Columns" = List.Accumulate(
        List.Zip({NamePerColumn, ValuesPerColumn, if TypePerColumn = null then {} else TypePerColumn)}),
        #"Added Index for fAppendColumns",
        (table as table, NameValuesType as list) => let
            values = NameValuesType{1},
            #"Value Type" = if (TypePerColumn = null) then
                Value.Type(values{0})
            else
                NameValuesType{2}
        in
            Table.AddColumn(table, NameValuesType{0}, each values{[IndexForAppendColumns]}, #"Value Type")
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Columns", {"IndexForAppendColumns"})
in
    #"Removed Columns"

From here, as long as there aren't any typos, you can do something like the following:

let
    Source = YourTable,
    // make sure we're sorting by period
    #"Sorted Rows" = Table.Sort(Source, {{"Dim_period", Order.Ascending}}),
    // Assuming we want to get deltas per account. This creates one row per account, where "Subtable" is a column that contains all of the rows for that account, sorted by period thanks to the previous line.
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ACCOUNT_CODE"}, {{"Subtable", each _, type table}}),
    #"Appended Deltas" = Table.TransformColumns(
        #"Grouped Rows",
        {"Subtable", each fAppendDeltaColumn(_, "Value")}
    ),
    #"Combined Subtables" = Table.Combine(#"Appended Deltas"[Subtable])
in
    #"Combined Subtables"