CODEWITHSUNDEEP
excelpowerquery
Gerd
Gerd

Reputation: 138

Change a column in PowerQuery based on another column

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 ?

Upvotes: 0

Views: 1921

Answers (1)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

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.

Upvotes: 1

Related Questions