Test for a number

[tempranello]

Hi there

I must be losing it...this seems such a simple thing to do, but I'm blowed if I can sort this out myself.

I've a column of type text in a query that may or may not contain text or a number.  I've an if statement in which I'd like to take the content of that column and act one way if the column's content is a number and another way if the content is text.

Number.FromText seems like a good place to start as it returns an error if the value is text, but I can't evaluate the error to a boolean value to trigger the if statement either way.  So perhaps I'm barking up the wrong tree.

In summary:

If column IsANumber then ...

else ...

Any thoughts?

Thanks!

[chrisu · Accepted solution]

I think you want to use Value.Is.  To check if a value is text, it would look something like this:

Value.Is([Column],type text)

To check for a number, it would be:

Value.Is([Column], Int64.Type)

Value.Is returns a boolean true/false, so you can wrap it in an IF.  

[tomasMoreno]

Another take on this, applied to an [Index] column: Divide the value by the "TestMultiple" to test, then round to 0 decimals, then multilply back again and test if resulting value is equal to original value:

[Index] being any column with numbers

[TestMultiple] the factor: 3, 5, 7, etc....

Number.Round([Index]/TestMultiple,0)*TestMultiple=[Index])

[sang_moneta]

I did not get good results with Value.Is as I did not change the Column to numeric yet. What helped me is this:

try Number.From([Column1])-Number.From([Column1]) otherwise 1

This will be 0 if Column1 is numeric no matter what kind of number it is, and 1 in case of errors. You can add bunch of these together to validate all columns once. If 0 no issues, anything othern than 0 there is an issue. Make sure to replace null values to 0 beforehand on any columns that you are validating.

[v-caliao-msft]

Hi Tempranell,

Please try to add a column use the power query below.
=try Number.From([ColumnA]) otherwise 0

Then convert this new created column from text to number

Then add a column using the DAX below.
Text = IF(ConvertToNumber[Number]=0,ConvertToNumber[ColumnA],"")

Reference
https://msdn.microsoft.com/en-us/library/mt253315.aspx

Regards,

Charlie Liao

[chrisu · Accepted solution]

I think you want to use Value.Is.  To check if a value is text, it would look something like this:

Value.Is([Column],type text)

To check for a number, it would be:

Value.Is([Column], Int64.Type)

Value.Is returns a boolean true/false, so you can wrap it in an IF.  

[Anonymous]

Worked for me. Thank you!

if Value.Is(AlphaNumeric, type text) then AlphaNumeric else Number.ToText(AlphaNumeric))

[Anonymous · Accepted solution]

Worked for me. Thank you!

if Value.Is(AlphaNumeric, type text) then AlphaNumeric else Number.ToText(AlphaNumeric))

[wonga]

Along with @chrisu suggestion, you could also do a try, otherwise statement:

try Number.FromText([TEST_COL]) otherwise "FALSE"

This will either return a number converted from text or the value of FALSE is TEST_COL is not a number at that particular row.

[wonga]

Are you trying to do this through DAX or Power Query?

DAX is used for Measures and Power Query is used for Calculated Columns.

[tempranello]

Apologies, I'm doing this in Power Query.

[wonga]

Can you provide some more context as to what you want to achieve? Provide a sample dataset and what you want the expected result to be.