Power Query Transform a Column based on Another Column

Question

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.

Answer 1

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.

Answer 2

Another alternative is this:

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

The code is a bit more readable, but can only be applied to one column at a time.

Most of it can be generated by using the UI, like shown here: http://www.thebiccountant.com/2017/07/23/transforming-a-column-with-values-from-another-column-in-powerbi-and-powerquery-in-excel/

Answer 3

To build on LoganTheSnowEater's answer, here is one method to retain the table column types using the second argument in Table.FromRecords as specified in the M Reference:

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

Edit: Updated to use much better method to extract table type from a table using Value.Type - also, embellished to modify multiple columns at once for completeness. Credit to this great convo for Value.Type: https://social.technet.microsoft.com/Forums/en-US/636e9b44-6820-4ff2-ab60-5dd6a5307bd2/type-conversion-mysteries

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