Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 IBERCONSA-FRIGALSA LA, RIOUXA S/N - TEIS VIGO-PONTEVEDRA, 36216 SPAIN, PT | 36216 SPAIN |
AVDA. EL CHOPO 4, 39311 SANTIAGO DE CARTES, CANTABRIA, SPAIN, ES | SPAIN |
AVDA. EL CHOPO 4, 39311 SANTIAGO DE CARTES, CANTABRIA, SPAIN, ES | SPAIN |
Result
ADDRESS | COUNTRY |
EDIFICIO IBERCONSA-FRIGALSA LA, RIOUXA S/N - TEIS VIGO-PONTEVEDRA, 36216 SPAIN, PT | SPAIN |
AVDA. EL CHOPO 4, 39311 SANTIAGO DE CARTES, CANTABRIA, SPAIN, ES | SPAIN |
AVDA. EL CHOPO 4, 39311 SANTIAGO DE CARTES, CANTABRIA, SPAIN, ES | SPAIN |
MY code : option text X is list not working
Table.ReplaceValue(#"Replaced Value8",each [COUNTRY], each
if Text.Contains([ADDRESS],{", PT",", ES"}) then "SPAIN" else
[COUNTRY], Replacer.ReplaceValue, {"COUNTRY"})
How to fix it
Thanks & Regards,
LucTP
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
hello, @LucTP
Table.ReplaceValue(
#"Replaced Value8",
each List.Contains(
{", PT",", ES"},
[ADDRESS],
(x, y) => Text.Contains(y, x)
),
null,
(v, o, n) => if o then "SPAIN" else v,
{"COUNTRY"}
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.