How to do addition of numbers to previous columns?

[AdityaG]

Hi community,

I would like to have the following input to be transformed in PowerQuery:

Starting number of output is defined, let's say, 400

Year	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032
Input	0	2	5	1	0	1	3	0	0	4
Output	400	402	407	408	408	409	412	412	412	416

It basically increments the starting number by the input number.

Can anyone help in getting DAX formula in Power Query for the above result.

Currently, I am just adding a custom column where output columns are [Year.1]

For eg. for column 2024.1, the expression is:

=[2024]+[2023.1]

For 2025.1,

=[2025]+[2024.1]

But this seems way to tedious considering I have high amount of data.

Can anyone help in providing DAX formula for the above results.

[AlienSx · Accepted solution]

Hi @AdityaG 

let
    data_table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKC1R0lEyAGIjIDYFYkMoH0QbQ9kgbKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"2023" = _t, #"2024" = _t, #"2025" = _t, #"2026" = _t, #"2027" = _t, #"2028" = _t, #"2029" = _t, #"2030" = _t, #"2031" = _t, #"2032" = _t]),
    source = Table.TransformColumnTypes(data_table,{{"Year", type text}, {"2023", type number}, {"2024", type number}, {"2025", type number}, {"2026", type number}, {"2027", type number}, {"2028", type number}, {"2029", type number}, {"2030", type number}, {"2031", type number}, {"2032", type number}}),
    starting_number = 400,
    input = Table.ToRows(source){0},
    input_list = List.Buffer(List.Skip(input)),
    count = List.Count(input_list),
    output_list = 
        List.Generate(
            () => [out = starting_number, i = 0],
            (x) => x[i] <= count,
            (x) => [out = x[out] + input_list{x[i]}, i = x[i] + 1],
            (x) => x[out]
        ),
    z = Table.FromRows({input, {"Output"} & List.Skip(output_list)}, Table.ColumnNames(source))
in
    z