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.

LoganTheSnowEater · Accepted Answer · 2015-07-22 11:32:17Z

24

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.

answered Jul 22, 2015 at 11:32

LoganTheSnowEater

6052 gold badges5 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Máté Juhász · Accepted Answer · 2024-09-18 08:54:11Z

11

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/

edited Sep 18, 2024 at 8:54

Máté Juhász

2,3552 gold badges24 silver badges46 bronze badges

answered Jul 23, 2017 at 6:07

ImkeF

1,5983 gold badges13 silver badges12 bronze badges

Comments

Dale Hohm · Accepted Answer · 2018-02-16 18:07:21Z

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

Mark · Accepted Answer · 2020-09-15 00:02:45Z

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

Michiel Soede · Accepted Answer · 2019-03-08 11:29:17Z

2

Sorry I was not so good editing the formula, this now works:

= Table.ReplaceValue(   #"Removed Columns", 
                   each [Contract Start Date],
                    each if [Contract Start Date] < #date(2019,01,01) then #date(2019,01,01) else
                          [Contract Start Date]
                    ,Replacer.ReplaceValue,
                    {"Contract Start Date"})

answered Mar 8, 2019 at 11:29

Michiel Soede

556 bronze badges

Comments

teylyn · Accepted Answer · 2015-07-21 21:37:35Z

0

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.

answered Jul 21, 2015 at 21:37

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

2 Comments

LoganTheSnowEater Over a year ago

The deal is primarily that I have to do this with several different tables, and it felt like a step that could possibly be done in 1 step vs several (i.e. Create new column, remove old column, rename new column). Additionally I have to do this with multiple columns per table and what's nice about TransformColumns is it can be applied to multiple columns at once whereas AddColumn adds one at a time. I have a potential solution where I convert the table to a list of records and use Record.TransformFields that I can share later.

teylyn Over a year ago

Still, you'll need a new column for the transform result.

Collectives™ on Stack Overflow

Power Query Transform a Column based on Another Column

6 Answers 6

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related