Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Date | Saving | Amount |
04/08/2022 | Yes | 2439.17 |
04/08/2022 | No | 1176.56 |
01/08/2022 | Yes | 791024.45 |
15/07/2022 | Yes | 11810.25 |
20/07/2022 | No | 12352.03 |
15/07/2022 | Yes | 81045.86 |
05/07/2022 | No | 365.95 |
05/07/2022 | No | 4509.88 |
15/07/2022 | Yes | 52011 |
06/07/2022 | Yes | 2922.4 |
15/07/2022 | No | 9592.56 |
15/07/2022 | Yes | 32471.67 |
15/07/2022 | Yes | 6793.62 |
15/07/2022 | No | 33938.1 |
15/07/2022 | Yes | 4390 |
15/07/2022 | No | 135385 |
15/07/2022 | Yes | 104310 |
04/07/2022 | Yes | 4598.01 |
14/07/2022 | Yes | 78597 |
Expected results:
Date | Saving | Amount |
04/08/2022 | Yes | 2439.17 |
04/08/2022 | No | -1176.56 |
01/08/2022 | Yes | 791024.45 |
15/07/2022 | Yes | 11810.25 |
20/07/2022 | No | -12352.03 |
15/07/2022 | Yes | 81045.86 |
05/07/2022 | No | -365.95 |
05/07/2022 | No | -4509.88 |
15/07/2022 | Yes | 52011 |
06/07/2022 | Yes | 2922.4 |
15/07/2022 | No | -9592.56 |
15/07/2022 | Yes | 32471.67 |
15/07/2022 | Yes | 6793.62 |
15/07/2022 | No | -33938.1 |
15/07/2022 | Yes | 4390 |
15/07/2022 | No | -135385 |
15/07/2022 | Yes | 104310 |
04/07/2022 | Yes | 4598.01 |
14/07/2022 | Yes | 78597 |
Solved! Go to Solution.
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.