How do I identify whether a column entry starts with a letter or a number using m code in power query?

Question

I have a column that contains either letters or numbers. I want to add a column identifying whether each cell contains a letter or a number. The problem is that there are thousands of records in this particular database.

I tried the following syntax:

= Table.AddColumn(Source, "Column2", each if [Column1] is number then "Number" else "Letters")

My problem is that when I enter this, it returns everything as "Letter" because it looks at the column type instead of the actual value in the cell. This remains the case even when I change the column type from Text to General. Either way, it still produces "Letter" as it automatically assigns text as the data type since the column contains both text and numbers.

Answer 1

Use this expression:

= Table.AddColumn(Source, "Column2", each if List.Contains({"0".."9"}, Text.Start([Column1], 1)) then "Numbers" else "Letters")

Note: It would have been smart to add sample data to your question so I wouldn't have to guess what your data actually looks like!

Answer 2

Add column, custom column with

= try if Value.Is(Number.From([Column1]), type number) then "number" else "not" otherwise "not"

Peter's method works if the choice is AAA/111 but this one tests for A11 and 1BC as well

Answer 3

Type that makes start

1. #. inStart

Use math code to multiply

2. /( 8 * 7 )\
3. answer = 56