How do I determine if value in text type column contains only numbers

[Pbix_is]

Hello!

I have a column type text where some values are only numbers and start with many zeros 00000000000123.

Other values are text only or are combination of text, characters and numbers; sample:

Goal is to trim first zeros, but ONLY from values that contain only numbers (so e.g. 00E2 should keep zeros after transformation, but above 00000000123 should be 123). 

I wast trying to duplicate column and change type of column to decimal number  -  then keep values from this column if there is no error, and from the original column if I get errors in the second column.

BUT some lines that contain text get transfered to number (these become zero):

Do you have a better idea to solve this or you know why these become zero? 

Thank you in advance!

[latimeria · Accepted solution]

Hi @Pbix_is ,

Try this:

Table.AddColumn(Source, "Custom", each try Number.ToText(Number.From(Text.TrimStart([Column1],"0"))) otherwise  [Column1], type text)

[Pbix_is]

Thank you!! It does solve all that I described above!

These came up, however, so I think I will add condition if text contains "E" or "." keep first column. 

[Pbix_is]

Oh, forgot to mention: in Power Query Editor