When I read NULL my first thought is Blank/Empty. Since people have different ways of implying a column may be empty, it is very common to need to cleanse out any “NULL”values.

For this Blog I am going to not only show how to handle a NULL value, I am also going to show you how to handle values that would imply NULL in a data source, such as a blank space in Excel, or an Oracle Essbase source where you would see a value like “#MISSING” or “#MI”.

The most common way to handle these issues would be using a Derived Column Transform in the Data Flow. Here is an example handling each of the scenarios I just mentioned-

(REPLACE(UPPER(<YourColumnName>),"#MI","0"))

I use UPPER because this is case sensitive so to guarantee we capture all of these values we need them all to be capitalized. Substitute “#MI” for any default value in your Data Source.

ISNULL(<YourColumnName>) ? "New Value" : <YourColumnName>

This will verify a NULL. From there you can change it to whatever you want. In this scenario you would still use a value that implies it needs to be updated, but the Destination Table may not allow NULLs.

ISNULL(<YourColumnName>) || TRIM(<YourColumnName>)=="" ? "New Value" : <YourColumnName>

This will work if you have a nice mixture of NULLs || (OR) a Blank space in your Data Source. You can add a value inbetween the quotation marks to substitute another value used.

TRIM(<YourColumnName>) == "" ? (DT_STR,4,1252)NULL(DT_STR,4,1252) : <YourColumnName>

This will turn a Blank space into a NULL value.

Example:

I’d also like to point out that the BIxPress Expression editor has these Expressions available in the Expression Editor, so you can always build your Expressions in there and Evaluate them as well to ensure you are getting the expected values returned.

Thanks for reading.