CODEWITHSUNDEEP
powerbipowerquerym
Peter Hui
Peter Hui

Reputation: 119

Power Query - Find and replace in one step instead of several

I have a simple column here.

enter image description here

I am using Power Query to replace some values,

1 becomes 119, 2 becomes 201, 3 becomes 321

There are no particular logic to it, it's to correct the mistyped numbers by users.

I did a find a replace function. Table.ReplaceValue(#"Changed Type",1,119,Replacer.ReplaceValue,{"Name"}) which worked great.

I want to combine the other find and replace functions into one single step for cleaner code.

I tried this but didn't work.

Table.ReplaceValue(#"Changed Type",{1,2},{119,201},Replacer.ReplaceValue,{"Name"})

I don't want to have 10 different find and replace steps in my PQ just want to have one step.

Upvotes: 0

Views: 1386

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

OPTION-1

Here below is the M code for all replace together-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    

    #"AllReplace" = [
        #"1" = "119", 
        #"2" = "201", 
        #"3" = "321"
    ],
    #"Replaced Value" = Table.TransformColumns(Source,{{"Column1",each Record.FieldOrDefault(AllReplace,_,_)}})
in
    #"Replaced Value"

Note: Need to number column as Text. You can convert the column to Number after the Replace Step.

Input

enter image description here

Output

enter image description here

OPTION-2:

You can also create a custom column using the below code-

if [Column1] = 1 then 119 else if [Column1] = 2 then 201 else 321

After creating the custom column, you can keep or remove the source coulmn.

Upvotes: 2

Related Questions