Forum Discussion
LucTP
Helper I
2 years agoReplace values with more text contains
Hi Everyone! How to Replace values with a text, where some text X contains in column A then Replate Column B values Y else value column A Example: ADDRESS COUNTRY EDIFICIO IBERC...
- 2 years ago
Hi LucTP, anoteher 2 versions of code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tYxBCsIwEEWv8sk6VdJKweWYTutgyIQkBqH0/tcwggsv4OrD4/2374YXWcWLQm6cvcZCw5plo1AIgSyy6PNFKOeIAZWloMmmQ9JYufGSuzLNo5tREkm0SNVY80PMYXdDbaETOMDfNSku/XOdnEOhWIU2xcLwlCsX2zdWumXp4W+SS0/+Kdat2hncOH140EYPIXMcbw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ADDRESS = _t, COUNTRY = _t]), ReplaceCountry_v1_Contains = Table.ReplaceValue(Source, each List.ContainsAny(Text.SplitAny([ADDRESS], " ,"), {"PT", "ES"}), each "SPAIN", (x,y,z)=> if y then z else x, {"COUNTRY"} ), ReplaceCountry_v2_EndsWith = Table.ReplaceValue(Source, each List.ContainsAny(List.LastN(Text.SplitAny([ADDRESS], " ,"), 1), {"PT", "ES"}), each "SPAIN", (x,y,z)=> if y then z else x, {"COUNTRY"} ) in ReplaceCountry_v2_EndsWith