Power Query: transform a column by multiplying by another column

Question

I want to do something similar to Power Query Transform a Column based on Another Column, but I'm getting stuck on how to modify the syntax for my particular goal.

Similar to the linked question, assume that I have the following table:

Table 1:
Column A | Column B | Column C
------------------------------
 1       | 4        | 7
 2       | 5        | 8
 3       | 6        | 9

Instead of changing the value of the Column A conditional on Column B, I want to multiply the values in multiple columns (Column B and Column C) by those in Column A and replace the values in the initial columns so that I can get the following:

Table 1:
Column A | Column B | Column C
------------------------------
 1       | 4        | 7
 2       | 10       | 16
 3       | 18       | 27

Is this possible to do without using multiple sequences of Table.AddColumn followed by Table.RemoveColumns?

I have also tried Table.TransformColumns based on this, but not been able to get the syntax right to achieve this.

Answer 1

Table.TransformColumns won't give you Column A unless you can index back into the table, which will only be possible if your columns only have unique data.

Table.TransformRows will let you build new rows with whatever logic you want:

let
    Source = Csv.Document("Column A,Column B,Column C
        1,4,7
        2,5,8
        3,6,9"),
    PromotedHeaders = Table.PromoteHeaders(Source),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"Column A", type number}, {"Column B", type number}, {"Column C", type number}}),

    MultipliedRows = Table.FromRecords(Table.TransformRows(ChangedType, 
        each [
            Column A = [Column A],
            Column B = [Column A] * [Column B],
            Column C = [Column A] * [Column C]
        ]))
in
    MultipliedRows

This works well for columns B and C, but if you need B through Z you might want fancier logic to avoid repeating yourself.

EDIT: A more general solution for many columns is to use Record.TransformFields on a list of transforms for all column names except "Column A".

let
    Source = Csv.Document("Column A,Column B,Column C,D,E,F
        1,4,7,1,2,3
        2,5,8,4,5,6
        3,6,9,7,8,9"),
    PromotedHeaders = Table.PromoteHeaders(Source),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"Column A", type number}, {"Column B", type number}, {"Column C", type number}, {"D", type number}, {"E", type number}, {"F", type number}}),

    MultipliedRows = Table.FromRecords(Table.TransformRows(ChangedType, (row) => 
        let
            ColumnA = row[Column A],
            OtherColumns = List.RemoveItems(Record.FieldNames(row), {"Column A"}),
            Transforms = List.Transform(OtherColumns, (name) => { name, (cell) => cell * ColumnA })
        in
            Record.TransformFields(row, Transforms)))
in
    MultipliedRows

Answer 2

I think the Table.AddColumn followed by Table.RemoveColumns is the usual and clearest way for this transformation. I'm also not happy with the fact that this results in so many steps in PowerQuery. But due to internal backfolding methods of PowerQuery this will usualy not result in better performance. (PowerQuery trys to give the main Work back to the queried Database if avaiable)

Answer 3

Assuming this doesn't need to be VBA and/or programmatic, you can just copy values in the first column, then highlight the values in the second column, and "Paste Special..." > Multiply.

That will produce the results in the same place you paste the multiplier.