Need help with a PowerQuery formula

[Craig]

Hello Experts,

Need help to write a powerquery formula that tests the following in power query editor

If the last 5 characters of a field are values (1..9) then extract into a new column the 2 characters immediately before the first value, otherwise leave the cell blank (null)

for example

existing field                                             new field (column)                                                 

PA19103                                                    PA

China                                                         null

CA95402                                                    CA

India                                                           null

thank you for any assistance!!

Craig

[adudani]

HI @Craig ,

Paste this in the advanced editor of a blank query:

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Inserted Last Characters" = Table.AddColumn(#"Changed Type", "Last Characters", each Text.End([Column1], 5), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Last Characters",{{"Last Characters", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Last Characters", null}})
in
#"Replaced Errors"

Please let me know if this resolves the question.

Appreciate a thumbs up if this is helpful.

[adudani · Accepted solution]

@Craig 

Alternatively: in 1 step: 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try  Number.From( Text.End([Column1],5)) otherwise null)
in
    #"Added Custom"

[Craig]

thank you very much...it was very helpful!!