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

[Blue_Carbon]

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?

[v-jingzhang]

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.