CODEWITHSUNDEEP
powerquery
Eugene
Eugene

Reputation: 1264

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

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!

Upvotes: 1

Views: 6624

Answers (2)

Eugene
Eugene

Reputation: 1264

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

Upvotes: 2

Sergey Lossev
Sergey Lossev

Reputation: 1530

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

Upvotes: 3

Related Questions