Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Decreasing value in the column

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.   ...
Need Help
  • Jakinta's avatar
    Jakinta
    4 years ago

    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"
Jakinta's avatar
Jakinta
Solution Sage
4 years ago

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"