Power Query: How to update column values in a table?

Question

Need your help with following problem:

I have a query with an Excel table as a source. This table has several user-input columns, which are now required to be multiplied by a coefficient from another column in this table. Column names should be retained.

Example:

let
    Source = Table.FromColumns({{1, 1}, {2, 2}, {null, 0.5}}, {"Clmn1", "Clmn2", "ClmnMultiplier"})
in
    Source

Is there any way to update their values like that:

row[Clmn1] = row[Clmn1] * IsNull(Row[ClmnMultiplier],1)
row[Clmn2] = row[Clmn2] * IsNull(Row[ClmnMultiplier],1)

or even better by applying a logic for a list of columns.

Sure I can do it by creating new columns, one by one, but I find this both not elegant and not productive.

Can anyone give me an advice on this? Thank you!

score 3 · Accepted Answer · answered Jul 20 '16 at 11:17

Alternative solution

let
    colNames = {"Clmn1", "Clmn2", "ClmnMultiplier"},
    Source = Table.FromColumns({{1, 1}, {2, 2}, {null, 0.5}}, colNames),
    transformRows = Table.TransformRows(Source, each Record.TransformFields(_, {
        {"Clmn1", (f)=>[Clmn1]*(if [ClmnMultiplier] is null then 1 else [ClmnMultiplier])},
        {"Clmn2", (f)=>[Clmn2]*(if [ClmnMultiplier] is null then 1 else [ClmnMultiplier])}
    }))
in
    Table.FromList(transformRows, Record.ToList, colNames)

By the way, you may precalculate
null_1=(if [ClmnMultiplier] is null then 1 else [ClmnMultiplier])
outside of function (in each row) and use it like this:

...
{"Clmn1", (f)=>[Clmn1]*null_1},
{"Clmn2", (f)=>[Clmn2]*null_1}
...

in order to not to calculate it twice

Well, yes, precalculating is good option and cleaner technique. You don't even need a function then: {"Clmn1", each [Clmn1]*null_1}, — Eugene, Jul 20 '16 at 11:35

Eugene · Answer 2 · 2021-05-28T19:48:00.590

I found the solution to the problem.

It is done with Table.TransformRows function. The obvious solution was to use Table.TransformColumns, but it turned out that you cannot reference the current row from it. Some search pointed to this solution:

let
    Source = Table.FromColumns({{1, 1}, {2, 2}, {null, 0.5}}, {"Clmn1", "Clmn2", "ClmnMultiplier"}),
    Transf = Table.FromRecords(
                Table.TransformRows(Source, 
                    (r) => Record.TransformFields(r,
                            {
                                {"Clmn1", each if r[ClmnMultiplier] <> null then _ * r[ClmnMultiplier] else _}, 
                                {"Clmn2", each if r[ClmnMultiplier] <> null then _ * r[ClmnMultiplier] else _}
                            })
                )
            )
in
    Transf

Many thanks to LoganTheSnowEater and his post

Power Query: How to update column values in a table?

2 Answers2

Alternative solution