Negate values in column

[azaterol]

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.

[OwenAuger · Accepted solution]

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