Power query transforms a column based on another column

Question

I keep thinking this should be easy, but the answer is shying away from me. In Excel Power Query, I would like to convert the value in each row of a column based on a different column value. For example, suppose I have table 1 as follows:

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

I would like to convert the values ​​in column A based on the values ​​in column B without adding a new column or replacing the original column A. I tried using TransformColumns, but the input can only be the value of the target column - I cannot access other field values ​​in row / records from 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 will result in:

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

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

Answer 1

Here is how I did it:

 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 

Thus, you can convert several columns at once using the nested list in the Record.TransformFields functions.

Answer 2

Kudos to LoganTheSnowEater ... I like your answer. You mentioned that it can be expanded in a few steps, and I wanted to publish an example of how I successfully used it for this:

 #"Patch Records" = Table.FromRecords(Table.TransformRows(#"Sorted Rows", (r) => Record.TransformFields( r, { {"Min Commit", each if r[service_id] = "21430" then 81 else if r[service_id] = "24000" then 230 else if r[service_id] = "24008" then 18 else if r[service_id] = "24009" then 46.9 else _}, {"Installed", each if r[service_id] = "21430" then 90 else if r[service_id] = "24000" then 230 else if r[service_id] = "24008" then 18 else if r[service_id] = "24009" then 52 else _}, {"Requested", each if r[service_id] = "21430" then 90 else if r[service_id] = "24000" then 230 else if r[service_id] = "24008" then 18 else if r[service_id] = "24009" then 52 else _}}))) 

My only surprise was that after I was done, I had to reset all data types, but that makes sense in retrospect.

ps I did not have enough reputation points to post this as a comment on the decision

Answer 3

You cannot convert an existing column with this step. Use a new column, do the conversion, and then delete the existing column. What a big deal? In Excel, you do not expect the formula to change the value of another column. In Power Query, the formula result is also stored in a column, and it cannot be a column that provides one of the formula input values.

Answer 4

Another option is as follows:

= Table.ReplaceValue (Table 1, 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 using the user interface, as shown here: http://www.thebiccountant.com/2017/07/23/transforming-a-column-with-values-from-another-column-in-powerbi -and-powerquery-in-excel /