Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 "bb...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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's avatar
Greg_Deckler
Community Champion
3 years ago

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"

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks a lot!