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.
I have a table that looks like this:
quantity | Salary | EBP | OH |
1 | 68 | 20 | 25 |
7 | 73 | 21 | 27 |
0.5 | 80 | 25 | 30 |
I want to multiply each of the Salary, EBP and OH columns by Quantity, resulting in a table that looks like this:
quantity | Salary | EBP | OH |
1 | 68 | 20 | 25 |
7 | 511 | 147 | 189 |
0.5 | 40 | 12.5 | 15 |
I know that I could beat it to death by adding 3 new columns and then removing the source columns. e.g.
= Table.AddColumn(#"Source", "salaryCost", each [quantity] * [Salary], Currency.Type)
= Table.AddColumn(#"addCol1", "ebpCost", each [quantity] * [EBP], Currency.Type)
= Table.AddColumn(#"addCo2", "ohCost", each [quantity] * [OH], Currency.Type)
= Table.RemoveColumns(#"addCol3",{"Salary","EBP","OH"}
But that is not very elegant.
I thought I might be able to do something like this:
= Table.TransformColumns( #"Source", {{"Salary", each Value.Multiply(_,[quantity])},{"EBP",each Value.Multiply(_,[quantity])},{"OH",each Value.Multiply(_,[quantity])}})
but I can't figure out how to get the value of [quantity] into Value.Multiply. Is this even possible?
Is there a better way?
Solved! Go to Solution.
=Table.ReplaceValue(#"Source",each [quantity],"",(x,y,z)=>x*y,{"Salary","EBP","OH"})
=Table.ReplaceValue(#"Source",each [quantity],"",(x,y,z)=>x*y,{"Salary","EBP","OH"})