trying to check whether a text column contains a number using M

[avininpowerbi]

I am handling a column , it has majorly text values but occasinally some number values that are similar to  1234.4567, 1234.5678 or 3222.7869 type numbers . ( pls note all of these numbers has four whole digits and 4 decimals) . All these numbers need to be mappe to a single name say X . 

I am using if Value.Is([Column], Number.Type) then "X" else "P".  but no luck so far 😞

can somone help pls. 

[tackytechtom]

Hi @avininpowerbi ,

How about this:

Here the code for the logic in the custom column:

try if Value.Is (Number.FromText([Column]), Decimal.Type) then "X" else [Column] otherwise [Column]

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[avininpowerbi]

Hi Tom, 

I did try , its threw all errors ! even the normal text has turned into errors. 

Any idea ? 

[tackytechtom]

Hi @avininpowerbi,

Could you provide a screenshot of the data as well as of the code for the custom column?

thanks 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[avininpowerbi]

Thanks for your help !  since I had no luck extracting the numbers, I used  Replace Value ( .00 as the string) with XYZ. Then after, I added a conditional column and used Text.contains . Thats it. 

[Daryl-Lynch-Bzy]

Hi @avininpowerbi  - you need to try converting the Text to number to find value, and include otherwise for text values.  Like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVorViVaqqKwC04ZGxiZ6pmbmFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Add Test", each try Value.Is( Number.From( [Text] ), Number.Type ) otherwise false)
in
    #"Added Custom"