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

     

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