Replace text in column that contains a certain string

[OptoGuru]

I am linking to an external web database in excel and using the power query editor to transform some cell contents using the "Replace Values" feature.  However, I want to find cells that contain a certain string value and then replace the entire cell with another string.  So an example column dataset would be:

bowl1

bowl2

bowl1

bowl5

bowl4

I would like to find the string "bowl" and then replace it with "Bowls"

It seems like I can only find a specific string and replace with another string. I assume there is an equation I can use for this, but couldn't quite find what it might be.

Thank you.

[dufoq3 · Accepted solution]

Hi @OptoGuru,

remove , Comparer.OrdinalIgnoreCase from my code if you want replace just bowl and not: Bowl, BOWL, bOWL etc.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsovzzFUitWJVipJLS6BsEBiRmCWoZExSBjGRihOTEoG045OziBBUzDHxNQMrt9EKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ReplacedValue = Table.ReplaceValue(Source,
    each [Column1],
    each "Bowls",
    (x,y,z)=> if Text.Contains(y, "bowl", Comparer.OrdinalIgnoreCase) then z else x,
    {"Column1"} )
in
    ReplacedValue