If you filter SharePoint list items where the column value is equal to null in Power Automate, check out this complete tutorial. Here, I will explain examples of “Power Automate get items filter query equals null‘ and “Power Automate get items filter query not equal null“.
To get items from a SharePoint list using the Power Automate get items filter query equals to null, you can use the Filter syntex for the Get items flow action, like, EmployeeFullName eq null.
Then, I will show you how to use an OData filter query not equal to null or empty Power Automate flow.
Power Automate Get Items Filter Query Equals null
I will show you how to use the get items filter query equals null in Power Automate.
Here, I have a SharePoint list with a few columns like Employee ID, Name, Contact number, etc. According to our requirements, we wanted to get all the items from the SharePoint list where the ‘Employee Full Name’ and ‘Employee Contact Number’ are null or empty.
The below is what the SharePoint list looks like:
Now, let us create our Power Automate cloud flow that will trigger automatically, where we can use the Power Automate filter query equals null condition.
1. Open the Power Automate home page, click on +Create -> Select “Instant cloud flow” -> Provide flow name, choose a trigger ”Manually trigger a flow” -> Click on Create.
2. After this, add a ‘Get items’ flow action under manual trigger flow, by using which we can get all the items from a SharePoint list. Then provide the below details:
- Site address: Select a specific site address from the drop-down.
- List Name: Select a specific SharePoint list from the drop-down.
- Filter Query: Provide the condition as placed below the code.
Since we want only the list items whose Employee full name and contact number are null, we need to add the below expression in the Get items Filter query option like in the screenshot below.
EmployeeFullName eq null and EmployeeContactNumber eq null
Note:Null Function will not works if we provide null function in below format:
EmployeeName eq ”
EmployeeName eq ‘null’
EmployeeName eq to the expression Null
EmployeeName eq to the expression ‘null’
3. we can use a Create HTML table flow action to view filtered SharePoint list items in Power Automate. Add a ‘Create HTML table’ flow action and set the details below for this.
- From: Take value from dynamic content.
- Columns: Select column options from the drop-down.
- In the custom column, create the below column table as displayed in the image below.
Employee Full Name
|Click on Dynamic Content -> Select values under dynamic content. Provide (No Name) and ‘0’ manually.
4. Now, it’s time to save and test the flow. Click on Save and Test it Manually.
5. If there are no errors, the flow runs successfully, and the body of the HTML table shows the SharePoint list of items where the ‘Employee Full Name’ and Employee Contact Number’ are null or empty, as in the screenshot below.
This is how to use an OData filter query equal to null or empty in the Power Automate Get Items flow action.
Power Automate Get Items Filter Query not equal null
Now, I will discuss how to use Power Automate get items filter query not equal to ‘null’ condition in a flow.
To explain this, I was using a SharePoint list, ‘Month End Discount Sale’, with different columns, as seen in the below screenshot. As per our requirement from the client, we wanted to get all the SharePoint list items, excluding null values in the ‘End of Sale Date’ column.
Follow the below step-by-step process:
Let us create an ‘Automated cloud flow’ in Power Automate that will trigger automatically.
1. Navigate to make.powerautomate.com -> click on +Create -> Select “Automated cloud flow” -> Provide flow name, choose a trigger ”When an item is created or modified” -> Click on Create. Set details like Site Address and List Name.
2. In the next step, add the ‘Get items‘ flow action to retrieve all the SharePoint list items based on the filter query condition. Then set the details below.
- Site address: Provide a specific site address from the drop-down.
- List Name: Set a specific list from the drop-down.
- Filter Query: Enter the condition as placed in the below code.
Power Automate Odata gets items filter query not equal null syntax will retrieve all the items from a SharePoint list excluding blank values in the ‘End of Sale Date’ column.
EndofSaleDate ne null
Where ‘ne’ refers to not equals to in the Power Automate Odata filter query syntax.
3. Add the ‘Create HTML table’ flow action to display the filtered SharePoint list items. In the table, select the list column details that should be visible.
- From: Enter Value from dynamic content.
- Columns: Select column options from the drop-down.
4. After creating the flow, Click Save and Test the flow. Test it Manually. After that, modify or create a new item in the SharePoint list.
5. If there are no errors, the flow runs successfully. Open the HTML table to view all the filtered SharePoint list items with no null values in the ‘End of Sale Date’ column, as shown below.
This is how to use the Odata filter query “not equals null” expression in Power Automate’s ‘Get items’ flow action.
I hope, from this Power Automate tutorial, you understood how to use the Power Automate ‘Get items’ filter query equal to null and the Power Automate get items filter query not equal to null or empty.
You may also like:
- Power Automate Odata Filter Query Startswith
- Power Automate Get Items Filter Query Date
- Power Automate OData Filter Query Yes/No
- Power Automate Get Items Filter Query Multiple Conditions
Preeti Sahu is an expert in Power Apps and has more than 6 years of experience working with SharePoint and the Power Platform. As a Power Platform expert for Power BI, Power Apps, Power Automate, Power Virtual Agents, and Power Pages, she is currently employed with TSinfo Technologies. She is the author of the book Microsoft Power Platform A Deep Dive. She also made a big technical contribution to SharePointDotNet.com in the form of articles on the Power Platform. She enjoys traveling and spending time with her family in her spare time.