How to use OData query filters in the SharePoint Get Items action for Power Automate

Summary

The Get Items action in Power Automate can be used for retrieving specific items from a SharePoint list. The Filter Query can be used for choosing which items to retrieve, but both the OData protocol and SharePoint Online lists have peculiarities that must be carefully considered. This article explains the Get Items action in detail, and provides several tips and tricks for filtering data from SharePoint to make Power Automate flows faster by reducing the amount of data being retrieved and leveraging SharePoint's index.

Steps

Project setup and SharePoint list details

1. In this example, we are using a custom list called People, which is part of the TrackMaster project, and contains the following columns:
   1. Full Name (renamed from Title) - single line of text
   2. Teams Account - single-value Person or Group
   3. Group membership - multi-value (multi-select) lookup, pointing to "Groups List" list which contains a Title, ID and other details. A user can be part of multiple groups.
   4. Active - yes/no (checkbox)
   5. Contact Type - single-value choice (drop down)
   6. Primary Team Engagement - single-value lookup, pointing to "Teams List" list which contains a Title, ID and other details
      
      

      Relevant columns from People list in TrackMaster

      Column (Display Name)	Type	Internal Name
      Full Name	Single line of text	Title
      Teams Account	Person or Group	TeamsAccount
      UniqName	Single line of text	UniqName
      Research Areas	Managed Metadata	Publication_x0020_Topics
      Contact Type	Choice	ContactType
      Group Membership	Lookup	Group_x0020_Membership
      Active	Yes/No	Active

       

2. Using a Power Automate flow with just a manual trigger, add a Get Items action.
   
   

Overview of Get Items action

1. Site Address - the URL of the SharePoint site. (In TrackMaster, where all Power Automate flows are part of a Solution/Project, we use an environment variable to set the site URL so that the solution can be deployed to different departments on their own SharePoint sites).
2. List Name - the SharePoint list from which to get data.
3. Limit Entries to Folder - Both document libraries and lists can use folders for organization. In the case of lists, "Enable Add Folder" must be checked in the list settings in order to use folders that will show in this field.
4. Include Nested Items - If using folders, Yes/No indicating whether to retrieve items in sub-folders or not.
5. Filter Query - An OData filter query that narrow down which items will be retrieved. This field is the primary focus of this article.
6. Order By - An OData sort query (optional).
7. Top Count - Limit how many items to retrieve. If combined with Order By, this can be used to bring the latest or oldest items only.
8. Limit Columns by View - A performance optimization option that will limit the columns retrieved to those set in a specific view. In TrackMaster, we normally create Power Automate-specific views that only contain the columns that are absolutely needed by all the flows accessing a particular list. This cuts down on the amount of JSON/XML data that Power Automate needs to retrieve from SharePoint and process inside the flow.

Overview of Filter Query option

1. The Filter Query field in Get Items actions allows filtering for specific items in a list. The filter must be specified as OData query within the limits of the SharePoint REST API.
2. General operator format:
   <Internal Column Name> <comparison operator> <value> <logical joins> ...
   
    
3. Comparison operators:
   1. lt - less than
   2. le - less than or equal to
   3. gt - greater than
   4. ge - greater than or equal to
   5. eq - equal to (numeric or string)
   6. ne - not equal to (numeric or string)
   7. Note that all these operators are lowercase
4. Functions:
   1. startswith(  column_name , 'string_to_check' ). Note that startswith is lowercase, the string value must be surrounded by single quotes, and the column name is provided before the string
   2. substringof( 'string_to_check' , column_name ). Note that substringof is lowercase, the string value must be surrounded by single quotes,  and the column name is provided after the string
   3. day()
   4. month()
   5. year()
   6. hour()
   7. minute()
   8. second()
   9. Special functions: some Power Automate functions can be evaluated when placed inside single quotes, such as 'utcNow()' to get the current date
5. Logical operators:
   1. and
   2. or
   3. not( )
   4. Note that all are lowercase
6. Special fields and property access:
   1. TaxCatchAll - virtual column used for searching Managed Terms in any column in the list, via TaxCatchAll/Term or TaxCatchAll/IdForTerm
   2. To access lookup fields, use ColumnName/Id for the numeric ID and ColumnName/Title for the display name. For example, the 'Group Membership' column can be filtered for the group MDEN, which has an ID=5, as either: Group_x0020_Membership/Id eq 5 or Group_x0020_Membership/Title eq 'MDEN'
   3. Similarly, to access properties of a Person or Group column, use:
      1. ColumnName/Title for the person's or group's name
      2. ColumnName/EMail for the email address (note that E and M are uppercase)
      3. ColumnName/Department for the department name
      4. ColumnName/JobTitle for the job title
7. Special columns and rules:
   1. Title - all lists are required to have a Title column, which remains the same even if the display name is renamed
   2. _x0020_ - not really a column, but it should mentioned that OData uses internal SharePoint column names.
      1. Normally, this means that if a column was created with a space in it, the space will be translated to its hexadecimal ASCII equivalent.
      2. For example, 'Group Membership' would have an internal name of 'Group_x0020_Membership'.
      3. The internal names can be found by exporting the output of the Get Items action after retrieving an item, or by looking at the URL of the column settings page under list settings in SharePoint.
      4. If a column is renamed, only the display name changes (the name shown in the UI) but the internal name remains the same. For example, in Track Master, we initially called a column 'Publication Topics' but then renamed it to 'Research Areas.' The internal name still remains 'Publication_x0020_Topics'
   3. Yes/No columns return true/false, but to filter in OData, use 1 for true/yes and 0 for false/no.
8. Optimization Tips:
   1. Use parenthesis ( ) to group operators logically. E.g.: Title eq 'Gabriel Mongefranco' and UniqName eq 'mongefrg'
   2. Narrow down the results as much as possible with OData filters before bringing them into Power Automate. This leverages SharePoint's indexing engine, reduces network traffic, and reduces the amount of data that Power Automate will need to process and/or loop through, helping flows run faster
   3. If after narrowing down the results you still expect large amounts of data, use XPath functions and/or Filter actions to filter the data further. Avoid loops whenever possible.
   4. Be careful of using /Id and /Title to access multi-valued lookup columns, as they results may vary depending on the status of the column indexes, particularly when a list exceeds 5,000 items. Instead, consider this alternative approach using XPath: How to check if a SharePoint multi-select lookup column contains a specific value in Power Automate

Examples

1. Filter a simple Text column
   1. Look for values where UniqName = mongefrg OR mongefrg2
      (UniqName eq 'mongefrg') or (UniqName eq 'mongefrg2')
2. Filter a Yes/No column
   1. Look for Active users only
      Active eq 1
3. Filter a Managed Metadata / Terms column
   1. It is not possible to filter managed metadata columns (a.k.a., managed terms, enterprise keywords, or tags) in the Get Items action. However, you can filter for the existence of a specific keyword in any column by using TaxCatchAll (taxonomy catch all virtual column).
   2. Look for users with research areas of 'Other'
      (TaxCatchAll/Term eq 'Other')
   3. Another approach is to use an Xml View Query, which requires sending an HTTP request to SharePoint. This approach is more complex and may involve security risks.
4. Filter a single-value or multi-value Lookup columns
   1. Look for users who are part of the MCN (ID=4) or MDEN (ID=5) groups
      (Group_x0020_Membership/Id eq 4) or (Group_x0020_Membership/Title eq 'MDEN')
   2. See limitations under Optimization Tips. Consider this alternative approach using XPath: How to check if a SharePoint multi-select lookup column contains a specific value in Power Automate
   3. Look for users whose primary Team is Mobile Technologies Core
      (PrimaryTeamEngagement/Title eq 'Mobile Technologies Core')
       
5. Filter People or Group columns
   1. Look for users with a specific email address or whose full name begins with a specific text. (Note: at Michigan Medicine, names are stored as Last, First instead of First, Last)
      (TeamsAccount/EMail eq 'mongexxx@xxx.xxx.edu') or (startswith(TeamsAccount/Title, 'Mongefranco'))
      
      
       
   2. Filter for People columns (Office 365 user accounts) with specific departments or job titles
       (TeamsAccount/Department eq 'MM Eisenberg Family Depression') and (TeamsAccount/JobTitle eq 'Data Architect')
       
6. Filter single-value choice columns
   1. Find contacts whose contact type is staff, which is a dropdown value
      (ContactType eq 'Center Faculty and Staff')

Notes

• None.

Resources

• Microsoft Learn - Use OData query operations in SharePoint REST requests
• How to check if a SharePoint multi-select lookup column contains a specific value in Power Automate
• Overview of TrackMaster: Membership Tracking Tool for U-M Centers and Institutes

About the Author

Gabriel Mongefranco is a Mobile Data Architect at the University of Michigan Eisenberg Family Depression Center. Gabriel has over a decade of experience in data analytics, dashboard design, automation, back end software development, database design, middleware and API architecture, and technical writing.   |   |