Forum Discussion

LucTP's avatar
LucTP
Icon for Helper I rankHelper I
2 years ago
Solved

Replace values with more text contains

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:

 

ADDRESSCOUNTRY
EDIFICIO IBERCONSA-FRIGALSA LA, RIOUXA S/N - TEIS VIGO-PONTEVEDRA, 36216 SPAIN, PT36216 SPAIN
AVDA. EL CHOPO 4, 39311 SANTIAGO DE CARTES, CANTABRIA, SPAIN, ESSPAIN
AVDA. EL CHOPO 4, 39311 SANTIAGO DE CARTES, CANTABRIA, SPAIN, ESSPAIN

 

Result

 

ADDRESSCOUNTRY
EDIFICIO IBERCONSA-FRIGALSA LA, RIOUXA S/N - TEIS VIGO-PONTEVEDRA, 36216 SPAIN, PTSPAIN
AVDA. EL CHOPO 4, 39311 SANTIAGO DE CARTES, CANTABRIA, SPAIN, ESSPAIN
AVDA. EL CHOPO 4, 39311 SANTIAGO DE CARTES, CANTABRIA, SPAIN, ESSPAIN


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

 

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    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's avatar
    dufoq3
    2 years ago

    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

2 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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