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:   ADDRESS COUNTRY EDIFICIO IBERC...
  • AlienSx's avatar
    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
    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