So i have data in power query but I want to identify member IDs wherebthe last digit is an alpha character. So this would have first 9 characters as number and last digit as Alpha. There can be other numbers in the column that are either all numbers 4 or mix of numbers and alphabets. Below is the sample and i want to be able to identify all memberids where this situation is true and separate them.
123456789A
WEX5T67
123456TGY
W12356789Y
1234567894
3456789453267832
Hi Yaksh,
First, make sure your ID column is a text column.
I think you should use 3 conditions, text length = 10, rightmost 1 char is not numeric, left 9 chars are numeric:
= if Text.Length([ID])=10 then ((try Number.FromText(Text.Start([ID],9))>0 otherwise false)=true and (try Number.FromText(Text.End([ID],1))<0 otherwise true)=true) else false
try Number.FromText(Text.Start([ID],9))>0 otherwise false will return true if all 9 chars are numeric, the conversion to number will fail if any char is not numeric.
try Number.FromText(Text.End([ID],1))<0 otherwise true is a bit different: if the conversion to number fails, then it returns true (last char is text). If the conversion is successful (this will happen only if the last char is numeric), we need to return false in this case, because is not text.
"<0" comparison will always return false if last char is a number, because the number created will never be lower than 0.
