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