Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
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
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