How to handle Null or Blank in a filter activity logic expressions

Question

Hello,

I am trying to filter out the null record from previous lookup activity in azure synapse workspace. To do this, I have used the below mentioned expressions in filter activity. But I am not able to get rid of the null records in the filter activity output. I have used the coalesce() function also to do that.

FYI, I know there is IsNull expression in data flow activity, but I don't want to use Data flow in pipeline as the pipeline is small and dataflow execution is extra cost.

PFB, details and attached screenshot.

Approach used : Filter activity to filter null records using coalesce() function and without coalesce(). Expression:

@if(equals(coalesce(activity('LookupID').output.value[0].ID,''),''), true,false)
@if(equals(activity('LookupID').output.value[0].ID, null), false,true)
@if(equals(activity('LookupID').output.value[0].ID, ''),''), false,true)
With any of the above expressions, the filter output has all the records including null value records. Please share your suggestion if I am missing anything.

Answer 1

Hi @sachin gupta ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question.
As I understand your requirement , you want to filter out the records from source where Id value equals NULL. Please let me know if that is not the ask here.

Since you have defined @activity('Lookup ProductionID').output.value as items in filter. You can simply use this expression to get rid of NULL records:

@not(equals(item().ID,null))

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button and take satisfaction survey whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators