Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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"}
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
26 | |
25 | |
24 | |
13 | |
10 |
User | Count |
---|---|
24 | |
21 | |
18 | |
17 | |
10 |