replacepowerbimashup

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


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.


Solution

  • 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:

    enter image description here

    After:

    enter image description here