M formula to check if text is a number and avoid errors

[Lucian]

Hello,

I need for a table to test if the first 4 characters in a column are numeric, do something... else do something else.

For example, let's consider this single column table:

TestColumn

123456

456789

XY1234

AB4567

I need to add a custom column that tells based on the first 4 characters if the tested column is a number or not.

I have used this query with a formula:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U4rViVYC0uYWlmBmRCRIHMx0dAKJK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TestColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TestColumn", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Value.Is(Number.FromText(Text.Start([TestColumn],4)), Int64.Type) then "Is number" else "Not number")
in
    #"Added Custom"

but will fail with an error if the "extracted 4 characters" does not result into an number:

TestColumnCustom

123456	Is number
456789	Is number
XY1234	Error
AB4567	Error

So, is there a way to modify the formula to get the following result?:

TestColumnCustom

123456	Is number
456789	Is number
XY1234	Not number
AB4567	Not number

Kind Regards,

Lucian

[mlsx4]

Hi @Lucian 

It may not be the optimum solution but you can add intermediate steps:

1. Add a try to Number.FromText and write "Not number" if error

2. Then, create a conditional formula (if column = "Not number" then "Not number" else "Is number")

3. Remove extra columns not needed

let
    Origen = Excel.Workbook(File.Contents("C:\ex.xlsx"), null, true),
    Hoja1_Sheet = Origen{[Item="Sheet",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"TestColumn", type text}}),
    #"Primeros caracteres insertados" = Table.AddColumn(#"Tipo cambiado", "Primeros caracteres", each Text.Start([TestColumn], 4), type text),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Primeros caracteres insertados",{{"Primeros caracteres", "FirstChar"}}),
    #"Personalizada agregada" = Table.AddColumn(#"Columnas con nombre cambiado", "IsNumber", each try Number.FromText([FirstChar]) otherwise "Not Number"),
    #"Columna condicional agregada" = Table.AddColumn(#"Personalizada agregada", "isNumb", each if [IsNumber] <> "Not Number" then "Is Number" else "Not Number"),
    #"Columnas quitadas" = Table.RemoveColumns(#"Columna condicional agregada",{"FirstChar", "IsNumber"})
in
    #"Columnas quitadas"

[Lucian · Accepted solution]

Hi @mlsx4 ,

Thank you for your suggestion that could be a solution. My problem is that is not just a single if but multiple if inside if.

Meanwhile it seems that I found the solution that initially I've missed out (forgot): TRY... OTHERWISE...

So the following formula it seems it worked:

= Table.AddColumn(#"Added Custom", "Custom2", each if (try Value.Is(Number.FromText(Text.Start([TestColumn],4)), Int64.Type) otherwise false) then "Is number" else "Not number", type text)

And now it seems I've got the expected result:

Kind Regads,

Lucian