1

I have a table with quite a few numeric columns that I need to update multiplying their values by another column. For example, if the original table were like this:

+---------+---------+---------+------------+
| value 1 | value 2 | value 3 | Multiplier |
+---------+---------+---------+------------+
|     100 |      50 |      30 | 2          |
|     100 |      50 |      30 | 0.5        |
|     100 |      50 |      30 | 1          |
+---------+---------+---------+------------+

I'd need to update it like this: 

+---------+---------+---------+------------+
| value 1 | value 2 | value 3 | Multiplier |
+---------+---------+---------+------------+
|     200 |     100 |      60 | 2          |
|      50 |      25 |      15 | 0.5        |
|     100 |      50 |      30 | 1          |
+---------+---------+---------+------------+

This post explains how to do it one column at a time, and in my case this approach looks like this:

#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns", each [value 1], each [value 1] * [Multiplier], Replacer.ReplaceValue,{"value 1"}),
#"Replaced Value 2" = Table.ReplaceValue(#"Replaced Value", each [value 2], each [value 2] * [Multiplier], Replacer.ReplaceValue,{"value 2"}),
#"Replaced Value 3" = Table.ReplaceValue(#"Replaced Value 2", each [value 3], each [value 3] * [Multiplier], Replacer.ReplaceValue,{"value 3"})

Is there a way to do this, or something similar, in one single step?

WKQ
  • 53
  • 1
  • 5
  • 1
    You'll probably want [`Table.TransformColumns`](https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumns). Give that a try and let us know if you have any specific problems. It's always a good idea to take a look at the [Power Query/M function reference](https://learn.microsoft.com/en-us/powerquery-m/power-query-m-function-reference) for this sort of thing - language references exist for a reason, and Stack Overflow is not meant to replace them. – greggyb Sep 17 '19 at 15:18
  • Thank you @greggyb, I'll try that – WKQ Sep 17 '19 at 15:40
  • Thank you @AlexisOlson, you are right, this worked – WKQ Sep 18 '19 at 07:10
  • Following the solution in @AlexisOlson suggested post, my step now looks like this: #"Transform" = Table.FromRecords(Table.TransformRows( #"Renamed Columns", each [ #"value 1" = [value 1] * [Multiplier], #"value 2" = [value 2] * [Multiplier], #"value 3" = [value 3] * [Multiplier], #"Multiplier" = [Multiplier] ])) – WKQ Sep 18 '19 at 07:14

0 Answers0