alter values in one column based on a value in another column

[mangopop]

I would like to change the values to negative values in a column based on whether another column value is 0 or 1

I'm using the advanced query editor (Power Query?) but I'm stuck on a few things

Firstly I do not know how to reference a column value in an if statement, I've seen examples in brakets [field] but this doesn't work. My attemp below used syntax that works else where in other statements but this give an operator error 'Cannot apply to list and numbers'

Another issue is it seems to require the else statement, is it ok to return null or false? I don't want it to act on an else statement.

The final issue is that I don't know how reference the column data in the replacer so I've just used numbers in the example. What I really want to do is replace the value with the original value *-1 to make it negative. I come from a JS background so this syntax is confusing!?

My attempt

= if {"SOPInvoiceCreditTypeID"} > 0 then Table.ReplaceValue(#"Renamed Columns",10,900, Replacer.ReplaceValue,{"InvoiceCreditQuantity"}) else false

Thanks for looking.

[LaurentCouartou]

Did you use the advanced editor to write your formula? To familiarize yourself with the language, I suggest you stick to the UI for a while and see what expression it returns.

Also, you might want to take a look at the MSDN site for a quick introduction to M:

https://msdn.microsoft.com/en-us/library/mt270235.aspx

Using [MyField] is the correct way to reference a column named MyField, when used in the definition of a custom column.

You can return anything for the else part of an if expression. However, you usually want to return data of the same type in every branch of the if expression. null is best suited when you want to return no value. (It is of any type.)

The easiest way to do what you want to achieve is probably to use the Conditional column option in the tab Add a column. (The names may be a little off since I use the French UI.) You can also chose the Add custom column option. In the end, you should obtain something like this:

= Table.AddColumn(#"PreviousStepName", "MyNewColumn", each if [Col1]>0 then -1*[Col2] else null)

Note that each is sugar syntax for the definition of a function. You could also write:

= Table.AddColumn(#"PreviousStepName", "MyNewColumn",(row)=>if row[Col1]>0 then -1*row[Col2] else null )

[Greg_Deckler · Accepted solution]

I would just create a new column with a formula of:

= if [Indicator Column] = 1 then [Value Column] * -1 else [Value Column]

Then, you could remove your original column and rename this new column to that column name.

[mangopop]

Thanks, that has done the job and is easier to reason about this way