Query editor test string if numeric

Power Query
1

Hi - I am trying to transform some employee id data. The employee id values are text (alpha-numeric). Generally speaking, FTE’s have a six digit number. Contractors have a number preceded by the letters CON. There are occasional exceptions to this rule of thumb. See the table in my sample data spreadsheet.

What I am trying to do is transform the values which are highlighted in yellow in column A of my attached spreadsheet to the values highlighted in green, and leave all other values untouched.

Here’s the challenge: Some FTE employee id’s are less than 100000, and in this case they show up as a value with less than six digits. I am pretty sure I can use the text.padstart function to convert, for example, a four digit employee id such as 4212 to 004212. I think I can create an if then else statement that will measure the length of the employee id value and then I can determine how many zeros to prepend.

However, here’s where an exception comes into play. We have an employee id that is four alpha characters. I don’t want to prepend any zeros to this particular value. I have tried to find a function that checks to see if the first character in a string is numeric or not, which I think I could use to then ignore that row and move on to the next row, but I haven’t been able to find a function to do this, and I’m not sure how I can take existing functions to accomplish the same thing.

Any suggestions on how to accomplish this will be much appreciated.

Thanks!

Trey

Query Editor Test String Numeric Data.xlsx (11.8 KB)

2

Hi @trey.cook,

Paste this code into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcsxC8IwEIbh/5K5hYuR5DJaCSJCCiXgEIJoB6eCUPX3e5cL3cySl4f7clbHMaZzPIWYbtdxuoSpN71V3R8vXVafFfH7eN/paEt2Whjr9rKtxbo+X/MyE7Zg00BPk7Voa++9jDnYhjCkA5H8LN6hRRKQYNppUwVqsLTVNtLO1wPgYEAZAFCU8gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t, #"Updated Employee Number" = _t]),
    UpdateEmpNum = Table.AddColumn(Source, "Result", each if List.Contains( {"0".."9"}, Text.Start([Employee Number], 1))= true then Text.PadStart([Employee Number], 6, "0") else [Employee Number])
in
    UpdateEmpNum

I hope this is helpful.

1 Like
3

This is perfect and brilliant, Melissa. Thanks so much for taking the time to show me what to do.

Best Regards,

Trey

1 Like