How to replace a string value if another string value exists in the cell ?

[Anonymous]

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!

[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]

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"

[Anonymous]

Thanks a lot!

[Greg_Deckler · Accepted solution]

@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]

Thanks! I need it write with M please