Multiply column values depending values from another column

[Mederic]

Hello everyone,

I'm trying to apply 2 methods to multiply a column by -1 depending on the value of another column,
The method with Table.ReplaceValue works very well but I can't manage to use it with Table.TransformColumns

How do I write this code correctly?

What is the best method for this transformation?

Thanks in advance

Best regards

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Amount", Int64.Type}}),
    #"Multiplied Column" = Table.TransformColumns(#"Changed Type", {{"Amount", each [Amoun], each if [Type] = "C" then [Amount] * 1 else [Amount], type number}})
in
    #"Multiplied Column"

TypeAmount

D	792
D	730
C	342
D	208
D	862
D	305
C	463
C	241
D	217
C	305

[Mederic]

Hello @v-cgao-msft 

Thank you for your feedback,
I understand the difference between the 2 methods and that Table.ReplaceValue is the right one for this case.

Have a nice day

Best regards

[v-cgao-msft · Accepted solution]

Hi @Mederic ,

In the above case, it is not possible to use Table.TransformColumns() to get the other columns in the row. So you cannot use the value of the [Type] column as a condition.

Instead, you can use to create a new column (custom/conditional column) with the desired values and then remove the original [Amount] column.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum