If you have NULL values in your column, you won’t be able to perform functions on it. The way to get around this is by converting the NULL values to 0, or values from other columns, which is what the coalesce() function does.
The Coalesce() function accepts two or more arguments and returns the first non-null argument. The function must have at least 2 arguments.
COALESCE(column, replace_value, ….)
column: The column where you want null values replaced by the specified replace_value.
replace_value: The column or values you want to replace your null values with. You can have more than one replace_value.
To replace null values with coalesce function in the Data Pipeline, click the +Add Step button and choose Add Column from the dropdown menu. To replace any column name that could contain NULL values with 0, use the following formula:
coalesce(“Column Name”, 0)
You’ll likely want to hide your original column by adding a Hide Columns step after the Add Column step in the Data Pipeline.
As specified, you can also replace null values found in one column, with another column’s values. This would be carried out at the row level, meaning, if column A’s value is null in row 3 and you want to replace that null value with a value from column B, the function would replace the null from column A with the value from column B in row 3. To see an example of this in the pipeline, please view this video: