Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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 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
@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}})
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous , this video talks about list accumulate, see if that can help
https://www.youtube.com/watch?v=YWAMaas_1AU
This uses a lookup table. But I'm wanting to avoid that and instead do it all within M.