Forum Discussion

Mederic's avatar
Mederic
Post Patron
3 years ago
Solved

Multiply column values depending values from another column

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

D792
D730
C342
D208
D862
D305
C463
C241
D217
C305
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

  • Mederic's avatar
    Mederic
    Post Patron
    3 years ago

    Hello Anonymous 

    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