Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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"}
)
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
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"}
)
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
6 | |
6 | |
6 |