CODEWITHSUNDEEP
powerquerym
Martin
Martin

Reputation: 19

How to replace more values in one column

I am trying to replace 3 values in one column, but I would like to do it in one step instead of three steps. I don't want to have Replaced Value1, Replaces Value2 and Replaced Value 3.

Imagine you have in column Cars only these values: Volkswagen, Renault and Dacia. You want to replace them like: 

Volkswagen --> VW
Renault --> RN
Dacia --> DC

Is it possible to do it in one step instead of 3? I am trying to use statement Table.ReplaceValue

Many thanks

Upvotes: 0

Views: 537

Answers (2)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

One of the methods is creating RenameCars table like this:

enter image description here

After adding this table to PQ you may use following formula:

= Table.TransformColumns(YourTable, {"Cars", each
                         try RenameCars{[Name = _]}[Name_mod] otherwise _})

Another way (if your list of replacements is quite short) is using Record.FieldOrDefault function. In this case supporting table is not needed.

= Table.TransformColumns(YourTable, {"Cars", each
            Record.FieldOrDefault([Volkswagen = "VW", Renault = "RN", Dacia = "DC"],_,_)})

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40204

Similar to @Aleksei's answer, you can use the Table.ReplaceValue function instead if you prefer:

= Table.ReplaceValue(YourTable, each [Car], each RenameCars{[Name = [Car]]}[Name_mod], Replacer.ReplaceText, {"Car"})

This assumes you have the RenameCars table as well.

Upvotes: 0

Related Questions