Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mangopop
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
LaurentCouartou
Solution Supplier
Solution Supplier

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
Super User
Super User

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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.