Forum Discussion
Responsive ResidentM formula to check if text is a number and avoid errors
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
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
2 Replies
mlsx4Memorable Member
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"
LucianHi 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