Forum Discussion
How to replace a string value if another string value exists in the cell ?
Hello,
How to replace a string value if another string value is present in the cell? example :
Data = aaa bbb ccc ddd eee fff
I'm looking to replace only "eee" with "xxx-eee", only if "bbb" exists ont the cell?
Thanks!
Anonymous Well, in DAX that is simply:
Column = IF(SEARcH("bbb", [Column1],,0)>1,SUBSTITUTE([Column1],"eee","xxx-eee"),[Column1])Let me see if I can figure out the Power Query.
5 Replies
- Greg_Deckler
Community Champion
Anonymous Well, in DAX that is simply:
Column = IF(SEARcH("bbb", [Column1],,0)>1,SUBSTITUTE([Column1],"eee","xxx-eee"),[Column1])Let me see if I can figure out the Power Query.
- Greg_Deckler
Anonymous OK, here is an inefficent way to do it in Power Query but something that can be done completely from the UI.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVEhKSlJITk5WSElJUUhNTVVIS0tTitWByGGIxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"), #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","eee","xxx-eee",Replacer.ReplaceText,{"Column1 - Copy"}), #"Added Conditional Column1" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.Contains([Column1], "bbb") then [#"Column1 - Copy"] else [Column1]), #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column1",{"Column1", "Column1 - Copy"}) in #"Removed Columns1"- AlexisOlson
Super User
If you're willing to venture beyond the UI, you can recreate Greg_Deckler's solution within the replacement step.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVEhKSlJITk5WSElJUUhNTVVIS0tTitWByAH5SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Replaced Value" = Table.ReplaceValue(Source, each if Text.Contains([Column1],"bbb") then "eee" else null, "xxx-eee", Replacer.ReplaceText, {"Column1"}) in #"Replaced Value"
