Negate with Conditions

Hi
I have a Table where I want to negate the value for Codes A and B.

I tried Conditional Column but my only choices was Value and Column.

How do I do this?

Thanks

A 10
B 5
C 20
B -5

Hello @AllisterB,

Thank You for posting the query into the Forum.

You can try the code given below.

let
    Source = Excel.Workbook(File.Contents("C:\Users\harjoshi1\Desktop\Exp.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Code", type text}, {"Numbers", Int64.Type}}) ,
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Number Updated", each if [Code] = "A" or [Code] = "B" then -[Numbers] else [Numbers] )
in
    #"Added Conditional Column" 

I’m also attaching the screenshot below of my result as well as the PBIX and excel file for the reference.

After you negate the numbers you can delete the original column and can use newly updated column for your analysis.

Hoping you find this useful and helps you to achieve the desired result.

Thanks & Warm Regards,
Harsh

Exp.xlsx (8.8 KB)

Negate Numbers with Conditions.pbix (28.9 KB)

1 Like

Great one @Harsh :+1:

I was just wondering what @AllisterB would like to have happend in the last combination of the sample ? Should B -5 return a NEG value? If so, a slight change in code is required…

I hope this is helpful.

1 Like

Hello @Melissa,

Really “Thankful” to you for appreciating my work. :slightly_smiling_face:

Actually I used the given code while I was practicing the Financial Analysis and it helped me to assign a negative sign to the Expenses Figures.

It’s great to have an alternative option as provided by you. :clap: :+1:

Thanks & Warm Regards,
Harsh

1 Like

Thank You Harsh and Melissa
Your Solution was very helpful

Allister

Hello @AllisterB,

You’re Welcome. :slightly_smiling_face:

Glad we’re able to help you.

Thanks and Warm Regards,
Harsh