Power Automate Filter Query If Blank [All Column Types]

If your Power Automate flow is returning all list items instead of just the ones with empty fields — or throwing a cryptic error when you try to filter for blank values — you’re not alone. This is one of the most common stumbling blocks I see when people start working with the Get items action in Power Automate.

The short answer: use ColumnName eq null for most column types. But the longer answer is that different SharePoint column types handle blank differently, and if you use the wrong syntax, your filter either silently returns nothing or breaks entirely.

In this tutorial, I’ll walk you through:

• The exact filter query syntax for every major SharePoint column type
• Why eq null doesn’t always work, and what to do instead
• Real-world mistakes that trip people up (and how to fix them)
• A quick-reference table you can bookmark

Let’s get into it.

What Is a Filter Query in Power Automate?

When you use the Get items action to pull data from a SharePoint list, by default, it retrieves up to 100 items (or however many you set as the top count). If your list has 1,000 rows and you only want the ones with a blank field, loading all 1,000 and then filtering inside the flow is wasteful — and slow.

The Filter Query field (under Advanced options in Get items) lets you filter at the source using OData syntax. The query runs server-side, so only the matching rows come back to your flow. This makes your flow faster, cheaper (fewer actions), and easier to maintain.

Think of it like a WHERE clause in SQL — EmployeeDesignation eq null means “give me only the rows where EmployeeDesignation is empty.”

Quick-Reference: Filter Query Syntax for Blank by Column Type

Before diving into the step-by-step examples, here’s the cheat sheet I wish existed when I first started:

Important: Don’t use quotes around null. Writing ColumnName eq 'null' is wrong — that would look for items where the column literally contains the text “null”, not items that are empty.

The SharePoint List I’m Using

For all examples in this tutorial, I’m using an Employee SharePoint list with these columns:

• Employee ID – Title column (default)
• Employee Name – People Picker column
• Employee Designation – Single Line of Text column
• Date of Joining – Date/Time column
• Employee Status – Choice column
• Contact Number – Number column

Some rows have these fields intentionally left blank so we can test the filter queries. The goal is to identify and flag those blanks using a Power Automate flow.

How to Set Up the Power Automate Flow

For each example below, the base flow structure is the same:

1. Trigger: Manually trigger a flow
2. Get items action (from SharePoint) with a filter query
3. Apply to each – loop through the filtered results
4. Update item – mark the blank records (optional, but useful for auditing)

Let me walk through each column type separately, since the filter query syntax varies depending on the column type.

Power Automate Filter Query If Blank

Now, let’s discuss all the examples one by one.

Example 1: Filter Blank Single Line of Text Column in Power Automate

Use case: Find all employees with an empty EmployeeDesignation.

Steps:

1. Open Power Automate and create a new Instant Cloud Flow → choose Manually trigger a flow → click Create
2. Click + New step and search for Get items
3. Set the Site Address and List Name to your SharePoint list
4. Click Show advanced options and in the Filter Query field, type:

EmployeeDesignation eq null

5. Click + New step, search for and add Apply to each
6. In the Select an output from previous steps field, pass in value from the Get items action
7. Inside Apply to each, add an Update item action
8. Set the Site Address and List Name, then map:
   • ID → ID (dynamic content from Get items)
   • Title → Title (dynamic content from Get items)
   • EmployeeDesignation → type ##### as a flag to mark blanks

9. Save and run the flow

After the flow runs, go back to your SharePoint list. Any row that had a blank EmployeeDesignation will now show ##### — making it easy to spot during a data quality review.

Pro tip: If your column name has spaces (e.g., “Employee Designation” instead of “EmployeeDesignation”), use the internal name of the column, not the display name. You can find the internal name in SharePoint list settings by clicking on the column name and looking at the URL — it shows Field=InternalName. Spaces in the display name are usually replaced with _x0020_ in the internal name, so Employee Designation becomes Employee_x0020_Designation. Always use the internal name in filter queries.

Example 2: Filter Blank Choice Column in Power Automate

Use case: Find all employees where EmployeeStatus is blank (no option selected).

In the Get items action’s Filter Query field, type:

EmployeeStatus eq null

The rest of the flow setup is identical to Example 1. Inside the Update item action, set EmployeeStatus to ##### (or whatever placeholder makes sense for your scenario).

Choice columns store null when nothing is selected — they don’t store an empty string. So eq null is the right approach here, and it works reliably.

Example 3: Filter Blank Number Column in Power Automate

Use case: Find all employees where ContactNumber is blank.

In the Get items action’s Filter Query field, type:

ContactNumber eq null

Inside the Update item action, set ContactNumber to 0 to replace blanks with a default value.

Number columns in SharePoint store null when empty (not 0). This is worth keeping in mind if you’re doing numeric calculations downstream — a blank is not the same as zero, and treating them differently matters.

Example 4: Filter Blank Date/Time Column in Power Automate

Use case: Find all employees where DateofJoining is blank.

DateofJoining eq null

Date columns can be tricky. A common mistake I’ve seen is writing:

DateofJoining eq 'null'   ← Wrong, this looks for the text "null"
DateofJoining eq ''       ← Wrong, empty string doesn't apply to dates

Neither of those will work. Stick with eq null (no quotes) and you’ll be fine.

Example 5: Filter Blank People Picker Column in Power Automate

People Picker columns are where most people run into trouble. You can’t just write ColumnName eq null for a Person column — it won’t work. People Picker is a complex/composite column type in SharePoint, and you need to filter on one of its sub-properties.

Use case: Find all employees where EmployeeName (People Picker) is blank.

EmployeeName/Claims eq null

The /Claims part accesses the person field’s internal identifier property. This is what SharePoint uses to store whether a person has been selected or not.

If you want to filter by a specific person’s email instead, you’d use:

EmployeeName/EMail eq '[email protected]'

But for blank checks, /Claims eq null is the one you want.

Example 6: Power Automate Filter Blank Lookup Column

Lookup columns also need special handling. Instead of using the column name directly, you add Id it at the end:

Use case: Find rows where a lookup column Department is blank.

DepartmentId eq null

The Id suffix tells the OData query to look at the lookup ID value (the integer stored internally). When a lookup is blank, this ID is null.

If you try Department eq null on a lookup column, you’ll likely get an error or unexpected results.

Why Your Blank Filter Query Might Not Be Working in Power Automate

Here are the most common reasons filter queries for blank values fail — I’ve hit most of these myself:

1. You’re using quotes around null
ColumnName eq 'null' is wrong. null without quotes is a keyword, not a string value.

2. You’re using an empty string instead of null
ColumnName eq '' works in some scenarios (it finds empty strings), but SharePoint typically stores a missing value as null, not as an empty string. These are different things.

3. You’re using the display name instead of the internal column name
If your column is called “Date of Joining” with spaces, the internal name might be Date_x0020_of_x0020_Joining. Check SharePoint List Settings → click the column → look at the URL parameter Field=.

4. People Picker columns need /Claims
EmployeeName eq null will not work. Use EmployeeName/Claims eq null.

5. Lookup columns need Id appended
DepartmentLookup eq null won’t work. Use DepartmentLookupId eq null.

6. Date columns are case-sensitive
The word null must be lowercase in filter queries. Writing NULL or Null can sometimes cause issues depending on the connector version.

7. Your column name has special characters
Hyphens, brackets, and special characters in column names will break filter queries. If you must use them, wrap the column name in square brackets — though the safest fix is to rename the internal column name in SharePoint.

Power Automate Combining Multiple Blank Checks in One Filter Query

You’re not limited to filtering on a single blank column. You can combine conditions using and / or.

Example: Get items where both EmployeeDesignation and ContactNumber are blank:

EmployeeDesignation eq null and ContactNumber eq null

Example: Get items where either field is blank:

EmployeeDesignation eq null or ContactNumber eq null

Example: Get items where EmployeeDesignation is blank but ContactNumber is not blank:

EmployeeDesignation eq null and ContactNumber ne null

Keep in mind that complex filter queries with many and/or conditions can sometimes hit OData query limits. If you’re getting unexpected results with complex queries, try breaking them into simpler conditions and filtering in a Condition action inside the loop instead.

A Practical Use Case: Weekly Data Quality Check

Here’s a real-world scenario where all of this comes together.

Say your HR team maintains an employee master list in SharePoint, and every Monday a flow should run to catch incomplete records before they sync to payroll. Some employees might be missing a designation, contact number, or onboarding date.

The flow would look like this:

1. Trigger: Recurrence – every Monday at 8 AM
2. Get items with filter query:textEmployeeDesignation eq null or ContactNumber eq null or DateofJoining eq null
3. Condition: Check if the value array from Get items is empty (length = 0)
   • If yes: Send a “No issues found” Teams message to HR
   • If no: Send an HTML table email with the list of incomplete records
4. Apply to each the results and optionally update a “DataQualityFlag” column to “Incomplete”

This kind of flow can save hours of manual auditing every week, and it’s entirely built on a single well-constructed filter query.

Frequently Asked Questions

Summary

Filtering for blank values in Power Automate isn’t complicated once you know the rules. Here’s the quick version:

• Most columns: ColumnName eq null
• People Picker: ColumnName/Claims eq null
• Lookup: ColumnNameId eq null
• Never quote null: eq null not eq 'null'
• Always use the internal column name, not the display name

The filter runs server-side, which keeps your flows lean and fast. Once you get comfortable with this pattern, you’ll find yourself reaching for it constantly — data quality flows, approval reminders, incomplete record reports — the use cases are everywhere.

Also, you may like:

• Power Automate Send Reminder Emails
• Create an HTML Table from an Array in Power Automate
• Add Excel Table into HTML Email Body using Power Automate Desktop

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.