Delete null rows in azure data factory data flow transformation

Question

I have created a Data Flow transformation in Azure Data Factory

Source : Excel file uploaded in Azure Blob Destination : JSON File created in Azure Blob

1. I do not want to insert null rows from source to target
2. If a particular row does not have a specific value then I want to insert value from earlier row.

I have tried to Filter transformation to filter out rows if all columns has null values using below expression , but it is not able to remove null rows

!(isNull(Column_1)) && !(isNull(Column_2)) && !(isNull(Column_3)) && !(isNull(Column_4)) && !(isNull(Column_5)) && !(isNull(Column_6))

Answer 1

Alternatively, you can use equals() to achieve what you want, like:

!equals(toString(Foo),'') && !equals(toString(Bar),'') 

Answer 2

I used filter transform and provided below filter on condition

!(isNull(Prop_0))||!(isNull(Prop_1))||!(isNull(Prop_2))||!(isNull(Prop_3))||!(isNull(Prop_4))||!(isNull(Prop_5))||!(isNull(Prop_6))||!(isNull(Prop_7))||!(isNull(Prop_8))||!(isNull(Prop_9))||!(isNull(Prop_10))

Answer 3

Use the columns() function in your Filter to get the value from all columns per row. Put that inside an array() and then use contains() to search the array for the existence of values (i.e. not null). Notice you have to coalesce the array elements to string:

contains(array(toString(columns())),!isNull(#item))

Put that in your Filter expression, should work.