Change field type and multiple exact value replacements in a single Power BI Step / M Statement

Question

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 (multiple steps) to change the field type to Text, then replace 3 exact values with another value. I'm struggling to do this in a more efficent/elegant way (in a single step).

#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"EU Member State", type text}}),
#"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.

Thank you!

Jay.

Answer 1

Table.replace can only perform one replace at a time, therefore you have to use alternative method to perform multiple replace within single m query, here is the solution and accept if help :)

#"Changed Type" = 'xxformula',
    #"Replaced Value" =(
    let
    Source1 = Table.FromColumns({{"0","1","2"}}),
    Substitutions = [
        #"0" = "unknown",
        #"1" = "yes",
        2 = "no"],
    Substituted = Table.TransformColumns(#"Changed Type", 
        {{"Name", each Record.FieldOrDefault(Substitutions, _, _)}})
    in
    Substituted
    )
    
    in
    #"Replaced Value"

Before:

After: