CODEWITHSUNDEEP
azure-data-factoryexpressionbuilder
Dimeji Olayinka
Dimeji Olayinka

Reputation: 81

Azure Data flow convert null to whitespace/blank

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?

Upvotes: 0

Views: 2634

Answers (1)

Joseph Xu
Joseph Xu

Reputation: 6063

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:

enter image description here

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

enter image description here

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

enter image description here

Summary:

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

Upvotes: 1

Related Questions