Decreasing value in the column

Question

Hello,&nbsp;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.&nbsp;Exampl:&nbsp;starting amount (costs) = 10 000 000&nbsp;Row NumYear NumSavingsExpansesDeclining Costs12018956 54725 623= 10 000 000 - Savings 1 + Expanses 122019966 47126 721= Declining Costs 1 - Savings 2 + Expanses 232020926 31924 834=&nbsp;Declining Costs 2 - Savings 3 + Expanses 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Can you help me? 🙂&nbsp;&nbsp;

jakinta · Accepted Answer

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"

Forum Discussion

Decreasing value in the column

1 Reply

Related content

Splitting Column values

Matching Column Values

Check column for similar values

INQUIRY: Powerquery Decreasing Value

Getting the increased or decreased value for Month Year column

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