cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jay82
Frequent Visitor

Replace multiple exact values in a single step

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.

1 ACCEPTED SOLUTION
hnguy71
Solution Supplier
Solution Supplier

@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

 

 

 

 

View solution in original post

4 REPLIES 4
hnguy71
Solution Supplier
Solution Supplier

@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

 

 

 

 

View solution in original post

Fowmy
Super User
Super User

@jay82 

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}})

  

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

amitchandak
Super User
Super User

@jay82 , this video talks about list accumulate, see if that can help

https://www.youtube.com/watch?v=YWAMaas_1AU



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

This uses a lookup table. But I'm wanting to avoid that and instead do it all within M.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!