How to Replace Nulls with Zeros

This tip will walk you through how to manipulate a column within Model, to replace NULLS with Zeros, using the convert and replace nodes within a data flow.

It is quite common to find NULL values in your tables, that means that there is no Data available for this Field. This can have unwanted effects on your visualizations and reporting as these field then do not get visualized and seem 'missing' e.g. Weeks with NULL do not show up. To fill this gap we show you how to replace these NULLs with zero values to 'fill' these reporting gaps.

How to do it:

1. The first step will be to bring the CONVERT node from the column operations, which is helpful when you want to change the data type of a given column and generate a new column that may not exist in the data source. 
2. Once you've connected the nodes together, go to your properties window, select the column you want to convert, and the data type you may want to convert it to. For this one, we'll convert it to a string data type.
3. Next, add the REPLACE node. Now we can replace the null values with zero values.
4. After the nodes have been connected, in the properties window, select the column you want to replace. Specify the value you want to look for in the column, which is null, and fill in the value you want to replace this with, which is zero. We'll keep the default values we have here for now and input a name for our converted field.
5. Let's then preview the data, and notice that now all the null values have been replaced with zeros in our converted Returns columns.
6. Lastly, convert the column back to integer since this is a measure data element that we want to be able to use in calculations.  We do so by using the CONVERT node one more time.  Select the column and the new data type, which will be integer.  Preview your data to verify.

Note: While this is a specific example for replacing NULL values, the CONVERT and/or REPLACE nodes are transformation functions within Model that are effective to use for data manipulation at the Model level.

If you have questions, let us know in a comment below.