Forum Discussion
Negate values in column
Hello everyone,
I would like to negate all numbers in the column that contain the status 1 and 2.
Current:
| Status | Number |
| 1 | 5 |
| 4 | 19 |
| 1 | 8 |
| 2 | 10 |
According to the formula:
| Status | Number |
| 1 | -5 |
| 4 | 19 |
| 1 | -8 |
| 2 | -10 |
I already have this formula in M for the number 1, but it doesn't work. Where is my mistake in thinking?
= Table.ReplaceValue(#"ReplacedValue",each [Number], each if [Status]=1 then each [Number] * -1 else [Number],Replacer.ReplaceValue,{"Number"})
I thank you for your help.
Hi azaterol
You have an extra keyword each before [Number] * -1
Try this instead:
= Table.ReplaceValue(#"ReplacedValue",each [Number], each if [Status]=1 then [Number] * -1 else [Number],Replacer.ReplaceValue,{"Number"})Also, you could handle Status 1 and 2 in the same step if you want, for example:
= Table.ReplaceValue(#"ReplacedValue",each [Number], each if [Status]=1 or [Status] = 2 then [Number] * -1 else [Number],Replacer.ReplaceValue,{"Number"})Regards
1 Reply
Hi azaterol
You have an extra keyword each before [Number] * -1
Try this instead:
= Table.ReplaceValue(#"ReplacedValue",each [Number], each if [Status]=1 then [Number] * -1 else [Number],Replacer.ReplaceValue,{"Number"})Also, you could handle Status 1 and 2 in the same step if you want, for example:
= Table.ReplaceValue(#"ReplacedValue",each [Number], each if [Status]=1 or [Status] = 2 then [Number] * -1 else [Number],Replacer.ReplaceValue,{"Number"})Regards