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