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?
