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
Get items Filter query Microsoft Power Automate Desktop

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
How to Get items Filter query Microsoft Power Automate Desktop

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.

See also  Power Automate OData filter query with examples

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

Get items Filter query using Microsoft Power Automate Desktop

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'
Power Automate Desktop SharePoint Get items Filter query

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.
SharePoint Get items Filter query using Microsoft Power Automate Desktop

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.
SharePoint Get items Filter query using Power Automate Desktop

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']%
SharePoint Get items Filter query in  Power Automate Desktop

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%
Get items Filter query in  Power Automate Desktop

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’.
Get items Filter query in Microsoft  Power Automate Desktop

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
How to Get items Filter query in Microsoft Power Automate Desktop

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.
Get items Filter query in Power Automate Desktop

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 list Get items Filter query

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.

See also  How to Convert Decimal to Integer in Power Automate?

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'
Microsoft Power Automate Desktop SharePoint list Get items Filter query

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

Microsoft Power Automate Desktop SharePoint  Online list Get items Filter query

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'
Microsoft Power Automate Desktop SharePoint Online list Get items Filter query

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 Online list Get items Filter query

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'
Power Automate Desktop Get items filter query

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 Filter query

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.

See also  Power Automate Desktop Mathematical Operations

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'
Get items Filter query Power Automate Desktop

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

Get items Filter query using Power Automate Desktop

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:

>