1

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.

Jay May
  • 41
  • 5
  • The change header name and header type is auto popluate in query editor function, why do you need to spend so many time to write this? If you want to replace all text in one text, try show some sample data and expected result – Kin Siang May 28 '21 at 13:55
  • The sample data is in the code above. The column was originally Numeric, but is now Text. Then changing the 0, 1, 2 values over to Unknown, Yes, No. I need a more elegant way of achieving this using M Code for use in Power BI. – Jay May May 29 '21 at 09:15

1 Answers1

0

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

Kin Siang
  • 2,644
  • 2
  • 4
  • 8
  • Think is the path I wanted to head, thanks for your help! I'm getting a Token Comma Expected error at IN in this string: ```{{"Name", each Record.FieldOrDefault(Substitutions, _, _)}}) IN Substituted)```. Thoughtgs? – Jay May May 30 '21 at 00:45
  • Your column name should be change based on your Bi , or other issue? The table name (#"Change typr")should be based on your advance editor also – Kin Siang May 30 '21 at 00:59
  • Sorry, I had an extra comma else where in the code. After review your suggested code worked like a gem, thank you Kin Siang! – Jay May May 30 '21 at 12:17
  • Welcome, glad to hear that, i also learn new thing :) – Kin Siang May 30 '21 at 12:18