Replace values with more text contains

[LucTP]

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

[AlienSx · Accepted 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"}
    )

[dufoq3 · Accepted solution]

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

[AlienSx · Accepted 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"}
    )