Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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"}
)
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.