Getting error while creating Custom Column

[gautampruthi]

Hi I am trying to create a custom column based on logic if sce_udfc is not blank then use the same value in new column else use the Course Code and assign the value to new column. i have created this code but getting error as "Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly."

IF(
NOT(ISBLANK([sce_udfc])),
[sce_udfc],
SWITCH(
[crs_code],
"BAS4WWCF3NWP", 5130,
"MAS3CCTH1NWP", 1700,
"MAS1EDON1NWF", 5100,
"MCS1PRPT1NWP", 1700,
"MCS1EDEC1NWP", 1700,
"RPS6EDUC1NWP", 2050,
))

[gautampruthi]

Hi @miTutorials Thanks for your prompt response, now i am getting error as "Expressions that yield variant data-type cannot be used to define calculated columns"

Both column is of Text Data Type

[Anonymous · Accepted solution]

Hi @gautampruthi 

Please try this for the new column

newcolumn =
IF (
    NOT ( ISBLANK ( [sce_udfc] ) ),
    [sce_udfc],
    SWITCH (
        [crs_code],
        "BAS4WWCF3NWP", "5130",
        "MAS3CCTH1NWP", "1700",
        "MAS1EDON1NWF", "5100",
        "MCS1PRPT1NWP", "1700",
        "MCS1EDEC1NWP", "1700",
        "RPS6EDUC1NWP", "2050"
    )
)

"Expressions that yield variant data-type cannot be used to define calculated columns" error occurs when it detects multiple data-types are returned in a calculated column formula. In original formula, [sce_udfc] column is of Text data type while the numbers (5130, 1700...) in SWITCH function are of whole number data type, so it leads to this error. To fix it, you can convert the numbers into Text data type by putting the numbers in double quotes. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[miTutorials]

Can you share sample pbix ?

[miTutorials]

I have checked the below and it is working, please test. Right click on the table and choose NewColumn.

Column = IF(
NOT(ISBLANK([Test])),
[Test],
SWITCH(
[Check],
"BAS4WWCF3NWP", 5130,
"MAS3CCTH1NWP", 1700,
"MAS1EDON1NWF", 5100,
"MCS1PRPT1NWP", 1700,
"MCS1EDEC1NWP", 1700,
"RPS6EDUC1NWP", 2050
))