Variant Data in Column - Error Message

[Anonymous]

Hello,

I have a column that is summed in hours that I download from a cube.  It has numbers, blanks and the word infinity.  I am trying to replace the blanks and infinity with a zero so I can convert it to a decimal field.  My syntax and error is below.  

Would welcome your help in resolving.   ---Thank you!

Avg Time to Complete = if(AND(Intake[Avg Time To Completion In Hours] = "Infinity",NOT ISBLANK(Intake[Avg Time To Completion In Hours])),0,(Intake[Avg Time To Completion In Hours]))

Receive:  Expression that yield variant data-type cannot be used to define calculated columns.

[MattAllington · Accepted solution]

This is the wrong approach. Go into the query editor (right click on the table, select edit query). Click on the column header and then select replace values and replace the items you need. Make sure you then set the number format before clicking close and load. 

[Anonymous]

This worked nicely.  Thank you!  Turns out I only needed to replace the word infinity, the nulls (or blanks) did not prevent me from converting the column to a decimal.

[Zubair_Muhammad]

@Anonymous

Give this a try

Average time to complete =
IF (
    OR (
        Intake[Avg Time To Completion In Hours]
            = ( 1 / 0 ),
        ISBLANK ( Intake[Avg Time To Completion In Hours] )
    ),
    0,
    Intake[Avg Time To Completion In Hours]
)

[Anonymous]

Thank you for your response.   After replacing the "infinity" with zeros, I tried your syntax.   I still have the word null in some of my fields which is considered text.  So when I try your syntax, I receive this message:

DAX comparison operations do not support comparing values of type Text with values of type Number.  Consider using the VALUE or FORMAT function to convert one of the values.

But I am good to go now.  If in edit queries, I right click on the column and use the replace option for the word infinity, then the convert to decimal works for me as it accepts the nulls.

Thanks again for your help.

[Zubair_Muhammad]

@Anonymous

or this

Average time to complete 2 =
IF (
    Intake[Avg Time To Completion In Hours]
        IN { 1 / 0, BLANK () },
    0,
    Intake[Avg Time To Completion In Hours]
)