Forum Discussion

OptoGuru's avatar
OptoGuru
New Member
2 years ago
Solved

Replace text in column that contains a certain string

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.

Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

1 Reply

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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