Forum Discussion

Blue_Carbon's avatar
Blue_Carbon
Frequent Visitor
3 years ago

Error: Expressions that yield variant data-type cannot be used to define calculated columns.

Hi everyone, I've added a formula column to the end of my power pivot data set and I keep getting this error.

The formula I am using is trying to strip PO numbers from a string of text if the PO number column isn't populated with a PO number.

This is the formula: 

 

Please could someone help?

Need Help

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Blue_Carbon 

     

    The error message means that the formula returns results of different data types in a calculated column, which is not allowed. From your formula, I guess your [PO Number] probably returns Number type value while the expression "MID(......)" returns Text type value, so you will get this error. If the PO Number should be a Number value, you can use VALUE function to wrap up the "MID" expression, for example, VALUE(MID(......)). VALUE function converts a text string that represents a number to a number.

     

    In addition, can you provide some sample data of the [PO Number] and [Journal Line Description] columns as well as the expected result in the new column? You use several SUBSTITUTE in the formula, I'd like to see if it's possible to simplify it a bit. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.