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 num...
Need Help
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.