Issue with Filter on Dataflow ADF

Question

Hello,

With the below data, I am adding a filter in the dataflow to restrict records that have Notes=Autocreate and Status = Admitted and Pending.

I am using the below in my filter activity, but it doesnt seem to work the way. Not sure what am i missing.

(Notes!="AutoCreated" && Status !="Pending") || (Notes!="AutoCreated" && Status != "Admitted")

If i use the above, these rows are filtered OUT, A123, A124, A127, A129, A130, A131. Not sure why that is happening.

My end result should be to have just A124,A125,A126,A127,A128,A131. Please advise on why the formula above is incorrect.

Thank you.

Answer 1

Hey sam nick!

It sounds like you're experiencing an issue with your filter expression in the Azure Data Factory dataflow. The expression you've written seems to be filtering out more records than you want.

From your description, you're trying to filter records that do not have Notes = "AutoCreate" and Status = "Admitted" or Status = "Pending". Here's your current filter expression:

(Notes != "AutoCreated" && Status != "Pending") || (Notes != "AutoCreated" && Status != "Admitted")

This expression would filter out any record that has either of those statuses if the Note is not equal to "AutoCreated". Hence, it’s likely excluding some records that you actually want to include.

To keep the records that you are interested in (those with Notes = "AutoCreate" and Status being either Admitted or Pending), you might want to use a different expression.

Suggested Filter Expression:

Try changing it to:

!(Notes == "AutoCreated" && (Status == "Admitted" || Status == "Pending"))

or, in a more approachable style:

Notes != "AutoCreated" || (Status != "Admitted" && Status != "Pending")

This filters to keep rows where Notes is not "AutoCreated" or where the Status is not either "Admitted" or "Pending".

Steps to Implement the Change:

Go to your filter transformation in the Azure Data Factory.

1. Replace the current filter expression with the suggested one above.
2. Save and re-run the data preview to check if you get the expected rows (A124, A125, A126, A127, A128, A131).

Additional Troubleshooting Tips:

• Verify that your data in the source contains the expected values for Notes and Status.
• Make sure your Debug Row Limit settings are appropriately configured to include enough rows for testing.

Hope this helps, and let me know how it goes!

References:

• Filter transformation in mapping data flow
• Troubleshoot mapping data flows in Azure Data Factory
• Mapping data flows in Azure Data Factory Overview

Thanks!

Kalyani