Decreasing value in the column

[Anonymous]

Hello,

I need to create a new column in Power Query, so that I have a starting ammount and the values in the new column are created by substracting using the values from the existing columns.

Exampl:

starting amount (costs) = 10 000 000

Row Num	Year Num	Savings	Expanses	Declining Costs
1	2018	956 547	25 623	= 10 000 000 - Savings 1 + Expanses 1
2	2019	966 471	26 721	= Declining Costs 1 - Savings 2 + Expanses 2
3	2020	926 319	24 834	= Declining Costs 2 - Savings 3 + Expanses 3

Can you help me? 🙂

[Jakinta · Accepted solution]

Here is one of many possible ways to do it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jcu7DQAxCAPQVRB1ivBPZkHsv8YFrrLkZ2ci4ULedF5cczCNLgycBWsl8u+33R005uAQTOMyzrv91TJLVjiiWPUB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row Num" = _t, #"Year Num" = _t, Savings = _t, Expanses = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"Savings", "Expanses"}),
    ChangedType = Table.TransformColumnTypes(#"Replaced Value",{{"Savings", type number}, {"Expanses", type number}, {"Row Num", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(ChangedType, "Declining Costs", each let s=List.Sum (List.Range(ChangedType[Savings],0,[Row Num]) ), e=List.Sum ( List.Range(ChangedType[Expanses],0,[Row Num]) ) in 10000000 - s + e)
in
    #"Added Custom"