Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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