cleaning up data in PowerQuery

[SOQLee]

I have a column in PQ that needs cleaning up. I'ld like to keep values that end with a combination of 1-letter and 4-digits.

Keep this pattern: ...A1234

Filter out this value pattern: ...A123

Help is appreciated. Thank you.

 

[VBA Geek]

Do you want it to end with one capital letter followed by 4 digits ?

let    
Source = Table.FromRecords( {[ID="A1234"], [ID="123456"], [ID="B98543"], [ID="A0122"], [ID="BB560"], [ID="TU2"]}, type table [ID=Text.Type] ),
    
    fnSpecialFilter = (S as any) as logical =>
        let
            Txt = Text.Reverse( Text.From( S ) ),
            Res = if
                     Text.Length (Txt) < 5
                  then
                      false
                  else
                      List.Accumulate( 
                            {0..3},
                            true,
                            (state, current) => List.Contains( {"0".."9"}, Text.Middle( Txt, current, 1) ) and state
                      ) and List.Contains( {"A".."Z"}, Text.Middle( Txt, 4, 1) )
         in
            Res,
                      
     FinalTable = Table.SelectRows( Source, each fnSpecialFilter( [ID] ) )
                          
           
 in
    FinalTable

 

[horseyride]

<cut>

 

[horseyride]

Whoops.
Meant to say, this adds a new column that you can filter to "True" to pick out any text ending with a letter [a-z,A-Z] then 4 digit number

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (Value.FromText(Text.Range([Column1], Text.Length([Column1])-4,4)) is number) and (Text.Range([Column1], Text.Length([Column1])-5, 1)=Text.Select(Text.Range([Column1], Text.Length([Column1])-5, 1),{"a".."z","A".."Z"} )))
in
#"Added Custom"

 

[SOQLee]

The pattern I want keep is any value that is a combination of 2 letters followed by 4 numbers, ie. AA1234.

 

[horseyride]

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (Value.FromText(Text.Range([Column1], Text.Length([Column1])-4,4)) is number) and (Text.Select(Text.Range([Column1], Text.Length([Column1])-5, 1),{"a".."z","A".."Z"} )<>"") and (Text.Select(Text.Range([Column1], Text.Length([Column1])-6, 1),{"a".."z","A".."Z"} )<>""))
in #"Added Custom"