Forum Discussion

tempranello's avatar
tempranello
Advocate I
10 years ago
Solved

Test for a number

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's avatar
    chrisu
    10 years ago

    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's avatar
    Anonymous
    8 years ago

    Worked for me. Thank you!

     

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

10 Replies

  • chrisu's avatar
    chrisu
    Responsive Resident
    10 years ago

    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.  

    • wonga's avatar
      wonga
      Continued Contributor
      10 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Worked for me. Thank you!

       

      if Value.Is(AlphaNumeric, type text) then AlphaNumeric else Number.ToText(AlphaNumeric))
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Worked for me. Thank you!

       

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

       

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    10 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

  • wonga's avatar
    wonga
    Continued Contributor
    10 years ago

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

     

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

      • wonga's avatar
        wonga
        Continued Contributor
        10 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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])