Forum Discussion

rdstone230's avatar
rdstone230
Regular Visitor
2 years ago

Extracting two letter State abbreviation from Address Field

I'm looking to extract the two letter State abbreviation from an address field that has an inconsistent pattern.  I tried filtering as: =List.Select(Address Field, each Text.Length(_) = 2) but the ...
Need Help
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi rdstone230, try this. It also handles Country Code with space cases (just for sure).

 

Result

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc3BCoJAFIXhVzm4dYTSUZmlGoWVFqiMIC6MxpBkDJ0Q3z7dJG1aXr7/cItCc9mGIuZIm7qu5ITk1TfyMeD6HCeCUyWHakDQqPmILnDo1jYJssTTSlJoeye39YDCF+ImhOpGSRB6qx9NN9BNhqi7V634Ne5wqh9yJEKqRooW/lupuYl3axMajBKkou+X9ylsZlG2cpYYFrMJzuEy4h7Y7Nv/jm9QfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Address Field" = _t]),
    Ad_State = Table.AddColumn(Source, "State", each 
        [ a = Splitter.SplitTextByAnyDelimiter(Text.ToList(" ,"))([Address Field]),
          b = List.Select(a, (x)=> Text.Length(x) = 2 and Text.Length(Text.Select(Text.Upper(x), {"A".."Z"})) = 2),
          c = List.Last(b)
        ][c], type text)
in
    Ad_State