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.
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.