Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Change column numerical value based on text value of another column

Hello,

I have the following table loaded in Power Query and i am trying to change the sign in the "Amount" column to negative if the value in the "Saving" column is "No", otherwise the Amount value stays unchanged/positive. Any help is much appreciated!

 

DateSavingAmount
04/08/2022Yes2439.17
04/08/2022No1176.56
01/08/2022Yes791024.45
15/07/2022Yes11810.25
20/07/2022No12352.03
15/07/2022Yes81045.86
05/07/2022No365.95
05/07/2022No4509.88
15/07/2022Yes52011
06/07/2022Yes2922.4
15/07/2022No9592.56
15/07/2022Yes32471.67
15/07/2022Yes6793.62
15/07/2022No33938.1
15/07/2022Yes4390
15/07/2022No135385
15/07/2022Yes104310
04/07/2022Yes4598.01
14/07/2022Yes78597

 

Expected results:

DateSavingAmount
04/08/2022Yes2439.17
04/08/2022No-1176.56
01/08/2022Yes791024.45
15/07/2022Yes11810.25
20/07/2022No-12352.03
15/07/2022Yes81045.86
05/07/2022No-365.95
05/07/2022No-4509.88
15/07/2022Yes52011
06/07/2022Yes2922.4
15/07/2022No-9592.56
15/07/2022Yes32471.67
15/07/2022Yes6793.62
15/07/2022No-33938.1
15/07/2022Yes4390
15/07/2022No-135385
15/07/2022Yes104310
04/07/2022Yes4598.01
14/07/2022Yes78597

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZG7DsIwDEX/JXPl+pnYP8GOqo7MDPy/RNQAggYvXo7PtZK7bQV1RV8ZmctSrrdHn6wSQK3sywlf7n0QtQpWB6VJbkHICmrHAtmK7XeByAmBB2f84iOexRhQMr3LauCv+3bWpRqEJVANA9yzaGMkGmqdIAcz6KwewWHB7z/5EyysjaC2jNcWApWTcJEQB8rkXhYmJomJ50WgCuGn5XOshQO+rs64uUV/z/4E", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Saving = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Saving", type text}, {"Amount", type number}},"en-GB"),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type", each [Amount], each if [Saving] = "Yes" then -[Amount] else [Amount], Replacer.ReplaceValue, {"Amount"})
in
    #"Replaced Value"

1 Reply

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZG7DsIwDEX/JXPl+pnYP8GOqo7MDPy/RNQAggYvXo7PtZK7bQV1RV8ZmctSrrdHn6wSQK3sywlf7n0QtQpWB6VJbkHICmrHAtmK7XeByAmBB2f84iOexRhQMr3LauCv+3bWpRqEJVANA9yzaGMkGmqdIAcz6KwewWHB7z/5EyysjaC2jNcWApWTcJEQB8rkXhYmJomJ50WgCuGn5XOshQO+rs64uUV/z/4E", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Saving = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Saving", type text}, {"Amount", type number}},"en-GB"),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type", each [Amount], each if [Saving] = "Yes" then -[Amount] else [Amount], Replacer.ReplaceValue, {"Amount"})
in
    #"Replaced Value"