title: Power Query Transform a Column based on Another Column
body: <p>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:</p>
  
  <pre><code>Column A | Column B
  -------------------
  X        | 1
  Y        | 2
  </code></pre>
  
  <p>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 <em>like</em> to be able to do something like this:</p>
  
  <pre><code>=Table.TransformColumns(Table1, {"Column A", each if [Column B]=1 then "Z" else _ })
  </code></pre>
  
  <p>which would result in:</p>
  
  <pre><code>Column A | Column B
  -------------------
  Z        | 1
  Y        | 2
  </code></pre>
  
  <p>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 <em>new</em> 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.</p>