Extracting two letter State abbreviation from Address Field

rdstone230 Table.AddColumn(#"Changed Type", "Custom", each let splitColumn1 = List.Reverse(Splitter.SplitTextByDelimiter(", ", QuoteStyle.None)([Column1])) in Text.Start(splitColumn1{1}?, 2), type text)

This should work even if some are missing the Country Code at the end. It looks for the last space or comma separated substring that consists of just two capital letters.

Note: Code edited to ensure two character string are, indeed, Letters.

   stateAbbrev = Table.AddColumn(#"Previous Step","State", (x)=>
        let 
            split = List.Reverse(Text.SplitAny(x[Address Field]," ,")),
            twoCaps = List.Select(split, each Text.Length(_)=2 and Text.Upper(_)=_ and (try Number.From(_))[HasError])
        in 
            twoCaps{0}, type text)

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