Change a column in PowerQuery based on another column

Question

I try to make in Excel PowerQuery the equal Operation to SQL Operation:

SET ColumnA = "Max" WHERE ColumnC = "George"

I know that I can CREATE a new Column with an the M expression

if ColumnA = "Max" then "George" Else "something"

but I don't like to create a third column as I do it again and again.

Any ideas how I could do this ?

Sergey Lossev · Accepted Answer · 2016-04-04 03:56:37Z

1

You can chain if statements together, like so

if [ColumnA] = "Max" then "George" else if [ColumnA] = "Jane" then "Janet" else "something"

You can also create a record that looks up names and then uses the alternate name if it exists. This can be done by creating a record like this in one query:

let
    Source = [Max = "George", Jane = "Janet"]
in
    Source

Let that query be called Names. Then, when you add a custom column, you can use this expression:

if Record.HasFields(Names, [ColumnA]) then Record.Field(Names, [ColumnA]) else "something"

Hope that helps.

edited Apr 4, 2016 at 3:56

Sergey Lossev

1,54011 silver badges21 bronze badges

answered Apr 1, 2015 at 20:57

Alejandro Lopez-Lago - MSFT

4,18422 silver badges25 bronze badges

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

3 Comments

Gerd Over a year ago

Thank you, both are good ideas and will help me in other situations. In the moment, I must cange ColumA again and again, but not in one step.

Gerd Over a year ago

I set an initial value and after some steps I must Update ColumnA, then some steps later, I must update it again. This column is for me a status field, I must update the status of some rows if my transformations did result in some special cases.

Alejandro Lopez-Lago - MSFT Over a year ago

If you have to do this for multiple steps, you can use Table.TransformColumns for each step to only modify ColumnA. For example, the second suggestion could be turned into:

Table.TransformColumns(stepName, {"ColumnA",each if Record.HasFields(Names, _) then RecordField.(Names, _) else "something"})

. This will change the cells in ColumnA to use the alternate name, and no new columns will be created. You can make this a function in a separate query and then invoke it multiple times to simplify your code.

Collectives™ on Stack Overflow

Change a column in PowerQuery based on another column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related