excelpowerquery

Power Query Transform a Column based on Another Column

I keep thinking this should be easy but the answer is evading me. In Excel Power Query, I would like to transform the value in each row of a column based on another column's value. For example, assume I have Table1 as follows:

Column A | Column B
-------------------
X        | 1
Y        | 2

I would like to transform the values in Column A based on the values in Column B, without having to add a new column and replace the original Column A. I have tried using TransformColumns but the input can only be the target column's value - I can't access other field values in the row/record from within the TransformColumns function. I would like to be able to do something like this:

=Table.TransformColumns(Table1, {"Column A", each if [Column B]=1 then "Z" else _ })

which would result in:

Column A | Column B
-------------------
Z        | 1
Y        | 2

I know there are ways to do this, but I'm trying to find one with the least amount of steps/transformations. For example, I know I could use Table.AddColumn to add a new Column A based on a function that looks at Column B, but then I have to remove the original Column A and replace it with the new Column A which requires multiple additional steps.

Solution

  • Here is how I ended up doing this:

    Table1:
Column A | Column B
-------------------
X        | 1
Y        | 2

= Table.FromRecords(Table.TransformRows(Table1,
    (r) => Record.TransformFields(r,
        {"A", each if r[Column B]="1" then "Z" else _})))

    Result:

    Column A | Column B
-------------------
Z        | 1
Y        | 2

    This way you can transform multiple columns at once by using a nested list in the Record.TransformFields function.