Microsoft Office 365

Filter null values in Dataverse using Power Automate

Filtering records on empty values can be a real pain as there are many mistakes to make.


First of all I would like to have a look at the null function. Many times, I’ve seen people ask the question why their null values haven’t been working. Below 3 examples. Only one will really work.

In Power Automate you can have an expression null.

You can type the text null (this is just 4 characters, n,u,l,l)

And then you can have empty strings (i.e. don’t fill in anything)

Mistake – Filter rows

Now how would we use the filter rows option to get just the records where a specified field is empty? You might want to try this:

Well the null function isn’t going to work like that! The above query will be evaluated as accountnumber eq

The eq operator has to have something on the right.

Also if you replaced the above with accountnumber eq ” you would only get the empty string values. which isn’t exactly the same as nothing. Huh???

When a field is not set Dataverse will not return that field at all and nothing is not the same as an empty string.

So how do we make this work?

Filter by null values – option 1

Now we can make use of FetchXml to filter by null values.

So for example if I wanted to find the accounts without an account number set I could use the following query:

Filter by null values – option 2

Then there is a second option to filter by null values (Thanks Marcel, see comments below)

accountnumber eq null

So by not typing the expression but the text null without quotes will also work.

Filter by not null values

And in a similar way I could also find all the records that do have an account number set:

All that is needed to filter your records are the null and not-null operators.

<fetch mapping='logical'>  
   <entity name='account'>   
      <attribute name='accountid'/>   
      <attribute name='name'/>   
      <attribute name='accountnumber'/> 
            <condition  attribute='accountnumber' operator='not-null'/>
Pieter Veenstra

Business Applications Microsoft MVP working as a Principal Architect at HybrIT Services Ltd. You can contact me using

View Comments

  • Hi Pieter,
    The filters are converted to OData filters. It is possible to do the filtering without using the fetchXML option.
    The correct notation would be: accountnumber eq null
    So not the expression but just as text

Recent Posts

1 Step to create the REPT function in Power Automate

Today I was asked on the chat about how to create a REPT function in…

2 weeks ago

4 avoidable pitfalls of Low Code development?

Low Code Development is all about anybody can do it! But what are the pitfalls…

4 weeks ago

Implement booking availability in Power Apps in less than 5 minutes

Today I was asked how to implement a booking availability solution in Power Apps. The…

3 months ago

Delegation with SharePoint lists Power Apps vs Power Automate

We all know about delegation warnings in Power Apps, but how about delegation with SharePoint…

3 months ago

Timing issues in relation to App OnStart, Screen OnVisible, Timers and user interruptions in Power Apps

When you create apps, it is important to understand the timing issues that may happen.…

3 months ago

Implement a PDF preview in a Model Driven Apps

Recently I needed to implement a PDF preview in a Model Driven App. This should…

3 months ago