Forum Discussion
alter values in one column based on a value in another column
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.
Solution SupplierDid 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 )