Filtering SharePoint Data with oData Queries in Power Automate

When working with SharePoint data in Power Automate, filtering your dataset efficiently is key to building optimized workflows. One of the most powerful ways to filter SharePoint data is by using oData filter queries in the Get Items action. This post will walk you through different examples of filtering various column types and using logical operators to refine your results. If you want to see all of the concepts from this blog post in action then check out the full length video where we go hand on with these and other examples of oData filtering. You can find the YouTube step-by-step video here: Filter SharePoint Data in Power Automate with oData

Understanding oData Queries

oData (Open Data Protocol) is a standard query language that enables filtering and querying data efficiently from multiple data sources, including SharePoint, Dataverse, and SQL Server. When used in Power Automate, it allows you to retrieve specific records without pulling unnecessary data, improving performance and efficiency.

For the examples in this post, we will be using the Power Automate Cloud Flow action "Get Items" to filter SharePoint list data. However, it’s important to note that oData queries are widely applicable across various platforms, including Microsoft Dataverse, SQL Server, and even third-party services that support the oData standard. Learning how to construct and apply these queries will benefit you in multiple automation and data retrieval scenarios.

An example of the Get Items action with an oData Filter Query for the Created by field in SharePoint

Filtering Different SharePoint Column Types

Each SharePoint column type has its unique characteristics and filtering them requires different approaches. Below are practical examples of how to filter single-line text, choice, lookup, number, and date columns using oData.

1️⃣ Filtering a Single Line Text Column

For a text-based column, you use the eq (equals) operator. The column name is case-sensitive and must match exactly as it appears in SharePoint.

Example:

Title eq 'Project X'

This retrieves all SharePoint list items where the Title column is Project X.

2️⃣ Filtering a Choice Column

Choice columns require the same syntax as text columns. Ensure you use single quotes around the value.

Example:

Department eq 'Sales'

This filters all items where the Department choice column is set to "Sales."

3️⃣ Filtering a Multi-Choice Column

Multi-choice columns can contain multiple values, so filtering them requires an exact match.

Example:

Skills eq 'Power BI'

This filters records where the Skills column contains exactly "Power BI."

For filtering multiple selections:

Skills eq 'Power BI' or Skills eq 'Excel'

This retrieves items where Skills include either Power BI or Excel.

Pro Tip: If you're unsure of a column’s internal name, check the List Settings in SharePoint or inspect the raw JSON output from a Get Items action.

4️⃣ Filtering a Lookup Column

Lookup columns reference values from another list. The default way to filter is using the ID of the lookup record.

Example:

DeptLookUp eq 3

This retrieves all records where the Department lookup field has an ID of 3.

If you prefer filtering by the title of the lookup field:

DeptLookUp/Title eq 'Marketing'

This pulls items where the related department’s Title is "Marketing."

5️⃣ Filtering by Number Values

Number fields support operators such as eq, gt, lt, and le.

Example:

Age gt 30

This retrieves all items where the Age column is greater than 30.

To filter within a range:

Age gt 30 and Age lt 50

This gets records where the age is between 31 and 49.

6️⃣ Filtering by Date Values

Dates in SharePoint require a special format: YYYY-MM-DDThh:mm:ss.

Example:

HireDate ge '2020-01-01T00:00:00'

This retrieves employees hired on or after January 1, 2020.

To find records within a year:

HireDate ge '2022-01-01T00:00:00' and HireDate le '2022-12-31T23:59:59'

This filters records where HireDate falls within 2022.

7️⃣ Filtering by Created By (Author) Field

The "Created By" field in SharePoint is internally referred to as Author. To filter based on this field, you need to use /Email or /Title depending on whether you want to match an email or a display name.

Example:

Author/Email eq 'user@example.com'

This retrieves all records created by user@example.com.

If filtering by display name:

Author/Title eq 'John Doe'

This pulls items where the Created By field matches "John Doe." Be mindful that display names are not always unique.

Using Logical Operators: and, or, Parentheses

oData allows for complex filtering by combining multiple conditions using and, or, and parentheses. Make sure you use them all lower case.

Combining Filters with AND

Department eq 'HR' and Age gt 25

This retrieves employees from the HR department who are older than 25.

Combining Filters with OR

Skills eq 'Power Automate' or Skills eq 'SharePoint'

This retrieves records where employees have either Power Automate or SharePoint skills.

Grouping Filters with Parentheses

(Department eq 'IT' or Department eq 'Finance') and Age lt 40

This retrieves all employees from IT or Finance departments who are younger than 40.

Conclusion

oData filtering in Power Automate is a powerful way to query SharePoint data efficiently. Understanding how different column types work and applying the correct operators ensures that your workflows run faster and return the most relevant data.

If you need help with this, remember we have support services where someone will hop on a screenshare with you for as little as 30 minutes to help. Just hist the Contact button and let us know how we can help.