expressions that yield variant data-type

[bhelou]

Hello Community ,

i made this formula and its makinf # error ( expressions that yeild variant data-type cannot be used to define calculated columns 

Employee_Name = IF(Employee_ID_Name[lookup_Name]="",Employee_ID_Name[EMPLOYEE_ID],Employee_ID_Name[lookup_Name])

* (Employee_ID_Name[lookup_Name] : is coming from a related formula and working

i have changed the lookup_name to ID the formula is working but when it is text its not 

I have changed the formula : replaced "" with blank() , didnt work 

I have changed the formula : replaced "" with 0, didnt work 

Thank you for help.

[v-xuding-msft · Accepted solution]

Hi @bhelou ,

I guess the column of EMPLOYEE_ID is number type and the lookup_Name is the text type. Your expression returns either number or text. But the data in calculated column need to be the same type. You could try using the function of FORMAT to return the EMPLOYEE_ID to text.

Employee_Name =
IF (
    Employee_ID_Name[lookup_Name] = "",
    FORMAT ( Employee_ID_Name[EMPLOYEE_ID], "" ),
    Employee_ID_Name[lookup_Name]
)