Azure Data flow convert null to whitespace/blank

Question

I am using an expression builder in derived column action of Azure data factory. I have an iif statement that that adds objects to a single array of objects based on whether 5 columns are null. Within the iif statement if the object is not null it adds it to the array object and I did not specify an action for when the columns is null. So if the 3 columns have a value then there should be 3 total objects in the array but the issue is for those 2 empty columns they show up as 2 "null" values within the array. I don't want that. I just want to cleanly have only the 3 objects in the array. How can I convert the null values to whitespace or is there a better way to get this done?

Answer 1

I've made a test to conver null value to whitespace successfully.

1. My source data is a csv file with 6 columns and some columns may contains Null value:

2. In the dataflow, I'm using Derived Column to convert the Null value.

3. In the data preview, we can see the Null value was replaced with whitespace/blank

Summary:

So we can use expression iif(isNull(<Column_Name>),'\n',<Column_Name>) to replace the NULL value to a whitespace.