Conversion issue from text to integer

[powerbidev123]

I have column YOB which has data type text. it has some blank values and whole number values . When I try converting the column to whole number I get an error which says cant convert '' to integer. I want to convert it to integer as I want to perform mathematical operation using this column. Any suggestion as to how I can convert the blank values to number as well

I tried replacing the blank values with 0 but still getting an error which is posted below:

And I need a calculated column because I cant do any changes in power query model as the data source is an online one and any changes in the structure in power query will cause an error the next time data is refreshed.

[aduguid · Accepted solution]

Try this one

YOB = 
VAR DOB = Query1[Date of Birth]
RETURN IF(
    ISBLANK(DOB) || DOB = "", 
    0, 
    VALUE(MID(DOB, 7, 4))
)

[aduguid · Accepted solution]

Try this one

YOB = 
VAR DOB = Query1[Date of Birth]
RETURN IF(
    ISBLANK(DOB) || DOB = "", 
    0, 
    VALUE(MID(DOB, 7, 4))
)

[powerbidev123 · Accepted solution]

Thanks a lot!! It worked 🙏 . This was the catch that I missed ISBLANK(DOB) || DOB = ""