Power Automate Desktop SharePoint Get items Filter Query

In Power Automate Desktop, Odata filter query is used to get the precise data from the SharePoint list based on specific conditions. For example, Task status eq completed, then it will retrieve the items from the SharePoint list whose task status is completed.

In this Power Automate Desktop tutorial, we will see how to filter different columns while getting items from the SharePoint list.

The columns that will be filtered are as follows:

• Choice
• Date
• Lookup
• A single line of text
• Person

For this, I have created a SharePoint list that contains details related to the Employee and which contains the following columns:

• Title-Single Line of text
• Name-Person
• Department-Choice
• Joining Date- Date/Time
• Designation- Single line of text
• Manager- Lookup column from the Manager list

Whereas the manager list contains the below columns in SharePoint.

• Title-Single Line of text
• Name- Single line of text
• Email address- Single line of text
• Phone Number- Number
• Department-Choice
• Job Title- Single line of text

Let us see how to implement the SharePoint Get items filter query for various types of columns like Choice column, Date column, lookup column, person column, etc.

Power Automate Desktop SharePoint Get items Filter Query [Choice column]

Here, we will get the employee name from the SharePoint list ‘Employee’ based on the query i,e. Department eq IT using Power Automate Desktop.

Now, we will create a desktop flow that will get items from the SharePoint list ‘Employee’ based on the query, i.e. Department eq ‘IT’. That means those employees from the IT department will be fetched, and the names will be displayed in the Message box.

Step 1: Open Power Automate Desktop and click on +New flow. Then, provide the flow name and click on Create.

Step 2: Now, we will get items from the SharePoint list; for this, drag and drop the Get items action. Then provide the below information:

• Site address: Provide the SharePoint site address
• List name: Provide the SharePoint list name

Then click on Advance options

• Filter query: provide the below query:

Department eq 'IT'

Step 3: Next, we will create a new list called “Employee”, so drag and drop the Create new list action. Then provide the below information:

• Here, change the variable name to Employee.

Step 4: Next, we will parse the JSON, so drag and drop the ‘Convert JSON to Custom object’ action to canvas and then provide the below information:

• JSON: Select the ‘GetItemResponse’ variable.

Step 5: Next, we will loop through each item and get the names of the Employee, so, drag and drop the For each action. Then provide the below information:

• Value to iterate: Provide the below argument:

%JsonAsCustomObject['value']%

Step 6: First, we will convert the current custom object to JSON; for this, drag and drop the ‘Convert Custom object to JSON’ action. Then provide the below information:

• Custom object: Provide the below argument:

%CurrentItem.Name%

Step 7: Now we will convert JSON to a custom object; for this, drag and drop the ‘Convert JSON to custom object’ and then provide the below information:

• JSON: Select the variable icon {x}, select the ‘CustomObjectAsJson’, and then provide the below information ‘CustomObjectAsJson’.

Step 8: Next, we will add items to the list; for this, drag and drop the Add item to list action. Then provide the below information:

• Add Item: Provide the below argument:

%JsonAsCustomObject.DisplayName%

• Into list: Provide the EmployeeName list

Step 9: Now we will display the output; for this, drag and drop the Display message action. Then provide the below information:

• Message box title: Provide the title for the message box.
• Message to display: Select the EmployeeName variable.

Step 10: Now run the flow by clicking on the run button, and you can see the employee names are popped in the Message box and are from the IT department.

Power Automate Desktop SharePoint Get items Filter Query [Date column]

Here, we will get the employee name from the SharePoint list ‘Employee’ based on the query i,e. Joining Date eq IT using Power Automate Desktop.

We will filter the Joing date column based on the date 2023-10-25 if the joining date of any employee is in the SharePoint list, then it returns the name of the employee

For this, in the above desktop flow, we will only change the Filter query field in the Get items action. The query is:

Joiningdate eq '2023-10-25'

Now, run the flow by clicking on the run button, and you can see the employee name whose joining date is ‘2023-10-25’.

Power Automate Desktop SharePoint Get items Filter Query [Lookup column]

Here, we will get an employee name from the SharePoint list ‘Employee’ based on the query i,e. Manager equals ‘Grady Archie.

To get the employee name whose manager is ‘Grady Archie’, we need to change the Filter query field with the below query in the Get items action.

Manager/Name eq 'Grady Archie'

Now, run the flow by clicking on the run button, and you can see the employee name whose Manager is ‘Grady Archie.

Power Automate Desktop SharePoint Get items Filter Query [Single line of text column]

Here, we will get an employee name from the SharePoint list ‘Employee’ based on the query i,e. Designation equals ‘Developer’ using Power Automate Desktop.

To get the employee name whose designation is Developer from the SharePoint list, for this, we will change the filter query field in the Get items action of Desktop flow. The query is

Designation eq 'Developer'

Now run the flow by clicking on the run button, and you can see the employee names whose designation is Developer.

Power Automate Desktop SharePoint Get items Filter Query [Person column]

Here, we will get an employee name from the SharePoint list ‘Employee’ based on the query the person’s email, i.e., Email is ‘LidiaH@szg52.onmicrosoft.com’ using Power Automate Desktop.

To get the employee name by filtering the email, we will change the filter query field in the Get items action of desktop flow and provide the below query.

Name/EMail eq 'LidiaH@szg52.onmicrosoft.com'

Now run the flow by clicking on the run button, and you can see the employee names whose email is LidiaH@szg52.onmicrosoft.com

Conclusion

In this Power Automate desktop tutorial, we saw how to implement filter queries on SharePoint list columns and retrieve items using Power Automate Desktop.

I have shown you how to use the get items filter query in Power Automate desktop for various columns like:

• Single line of text
• Choice column
• Date column
• Lookup column
• Person column, etc.

You may also like:

• Update Items in a SharePoint List in Power Automate Desktop
• Create and Delete an Item from a SharePoint List using Power Automate Desktop
• Get items from SharePoint List using Power Automate Desktop
• Convert Data Table Columns to List in Power Automate Desktop