Replace multiple exact values in a single step

[Anonymous]

Hi all

It's my first day starting out with Power BI and M Code. I've looked through a dozen replace threads, but couldn't find what I was looking for.

I'm currently using the below code to replace 3 exact values with another value. I'm struggling to do this in a more efficent/elegant way (in a single step).

#"Replaced Value" = Table.ReplaceValue(#"Changed Type","0","Unknown",Replacer.ReplaceValue,{"EU Member State"}),

#"Replaced Value1" = Table.ReplaceValue(#"Changed Type","1","Yes",Replacer.ReplaceValue,{"EU Member State"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value","2","No",Replacer.ReplaceValue,{"EU Member State"})

Any help would be greatly appreciated!

Note: I don't want to use a lookup table.

Thanks,

Jay.

[hnguy71 · Accepted solution]

@Fowmy has a great solution and I think that's what you're looking for. If you want a more advanced technique, you can use an array to find and replace bulk items. Here is a sample:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWMgSTRmDSsyQ1VyE8syRDIbggMTlVKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"EU Member State" = _t]),
    ReplaceArr = 
        [
            0 = "Unknown",
            1 = "Yes",
            2 = "No",
            #"Item With Space" = "Replace Item With Space"
        ],
    FindAndReplace = Table.TransformColumns(Source, {{"EU Member State", each Record.FieldOrDefault(ReplaceArr, _, _)}})
in
    FindAndReplace

[Fowmy]

@Anonymous 

You can use Table. Transform with IF instead.

Table.TransformColumns(#"Changed Type",{{"EU Member State", each  if _="0" then "Unknown" else if _=1 then "Yes" else if _=2 then "No" else _, type text}})

[amitchandak]

@Anonymous , this video talks about list accumulate, see if that can help

https://www.youtube.com/watch?v=YWAMaas_1AU

[Anonymous]

This uses a lookup table. But I'm wanting to avoid that and instead do it all within M.