Power Query Transform a Column based on Another Co

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

Kudos to LoganTheSnowEater... I love your answer. You mentioned that it could be expanded upon for multiple actions and I wanted to post an example of how I successfully used it to do so:

#"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 I had to reset all of the data types after I was done, but that makes sense in retrospect.

p.s. I didn't have enough reputation points to post this as a comment to the solution

Answer 3

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 4

To build on LoganTheSnowEater's answer, here is one method to retain the table column types by leveraging Table.Schema to construct a table type to use as the second argument in Table.FromRecords as specified in the M Reference:

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

= Table.FromRecords(
    Table.TransformRows(
        Table1,
        (r) => Record.TransformFields(r,
            {"Column A", each if r[Column B]="1" then "Z" else _}
        )
    ),
    Expression.Evaluate(
        let s = Table.Schema(Table1) in 
        "type table ["&
        Text.Combine(
            List.Transform(List.Zip({s[Name],s[TypeName]}),each "#"""&_{0}&""" = "&_{1}),
            ", ")
        &"]",
        #shared
    )
)

Table.Schema produces a table with column Name and column TypeName, which are used to construct the text for defining the table type, which in this case is

type table [#"Column A" = Text.Type, #"Column B" = Int64.Type]

Of course, it's possible to manually type in the table type instead, which you would need to do if the transformations changed the types of the values in the column.

Answer 5

You can't transform the existing column with such a step. Use a new column, do the transform, then delete the existing column. What's the big deal? In Excel you would not expect a formula to change the value of a different column, either. In Power Query, the result of a formula is also stored in a column, and that cannot be the column that provides one of the formula input values.