Filtering SharePoint Data with oData Queries in Power Automate

When working with SharePoint lists in Power Automate, filtering data efficiently is crucial to ensure seamless automated workflows. Otherwise, unnecessary information might end up getting processed, slowing down flows and taxing system resources.

If you want training delivered by our Microsoft 365, SharePoint, Power Platform, Azure, SQL, Office development, and Copilot AI teams, schedule your consultation now!

How oData Works in Power Automate

oData enables efficient data filtering by allowing you to construct queries that work across multiple data sources, such as SharePoint, Dataverse, and SQL Server. This means you can use a consistent syntax to retrieve data, making it easier to manage complex workflows. The main benefit is performance: by retrieving only necessary records, you reduce the amount of data processed, which is especially helpful for large datasets.

In Power Automate, the Get Items action is where you would typically apply oData filter queries. This action lets you pull specific items from a SharePoint list based on conditions you define, such as filtering by column values or date ranges.

Filtering Different SharePoint Column Types

Different SharePoint column types require specific approaches when filtering with oData queries. Here’s a breakdown of how to handle common types:

• Single Line Text Column: Use the eq (equals) operator to match exact text. For example, Title eq ‘Project X’ retrieves items where the title is exactly “Project X”.
• Choice Column: Similar to text, use eq with single quotes, like Department eq ‘Sales’ to filter items in the Sales department.
• Multi-Choice Column: These can be trickier, as they allow multiple selections. You can filter for items containing a specific value, such as Skills eq ‘Power BI’, but ensure you verify the internal column name in SharePoint settings for accuracy.
• Lookup Column: Filter by the lookup record’s ID, like DeptLookUp eq 3, or by title, such as DeptLookUp/Title eq ‘Marketing’.
• Number Fields: Use operators like eq, gt (greater than), lt (less than), and le (less than or equal). For example, Age gt 30 and Age lt 50 filters ages between 30 and 50.
• Date Values: Dates must be in the format YYYY-MM-DDThh:mm:ss. For instance, HireDate ge ‘2020-01-01T00:00:00’ gets hires from 2020 onward, and HireDate ge ‘2022-01-01T00:00:00’ and HireDate le ‘2022-12-31T23:59:59’ filters for 2022 hires.
• Created By (Author) Field: Use sub-properties like Author/Email eq ‘user@example.com’ or Author/Title eq ‘John Doe’ to filter by the creator’s email or display name.

Unexpected Detail: Beyond SharePoint

While the focus is on SharePoint, oData queries are versatile and can be applied to other data sources like Dataverse and SQL Server, offering a consistent approach to data retrieval across the Microsoft Power Platform. This flexibility can be a game-changer for integrating multiple systems in your workflows.

The Need for Efficient Filtering

If you’ve ever run a workflow in Power Automate and watched it crawl because it’s pulling way too much data from a SharePoint list, you’ll get why efficient filtering is a big deal. When you’re handling large lists, grabbing everything without a plan can slow things down and chew up resources. That’s where oData filter queries come in—they’re like a secret weapon built into the Get Items action, letting you set clear rules to grab only what you need, keeping your flows fast and user-friendly.

Without filtering, your flow might haul in every item from a massive SharePoint list, even if you just want a handful of records. It’s a drag on speed and a waste of effort. But with oData queries, you can tell Power Automate exactly what to fetch—like “only show me items from this department” or “just the records from last week.” It’s a straightforward fix that boosts performance and makes your work easier to manage.

Why It’s a Lifesaver

What we love about oData is how it cuts through the clutter. Instead of dragging every record into your workflow—which can bog things down, especially with big datasets—it lets you pinpoint just the items you care about. It’s like asking a friend to grab your favorite book from a shelf instead of dumping the whole library on your desk. That focus saves time, reduces strain on your system, and keeps everything running smoothly.

How It Works in Power Automate

In Power Automate, oData teams up with the Get Items action to make your life easier. You can write simple queries to fetch only the data that matters. Here’s what we mean:

• SharePoint Scenario: Say you’ve got a SharePoint list packed with employee info—names, departments, you name it. Without oData, Get Items might pull the whole list, even if you only need the marketing team. But toss in a query like department eq ‘Marketing’, and boom—you’ve got just those folks. It’s quick, clean, and keeps your workflow on track.
• SQL Server Twist: Or maybe you’re dealing with a SQL Server full of sales data. Why grab years of records when you only need last month’s? A query like transactionDate ge ‘2023-09-01’ narrows it down to the last 30 days, making your analysis sharper and faster.

The Professional Perk

Here’s the kicker: oData isn’t a one-trick pony. It works across different platforms in Power Automate, so whether you’re juggling SharePoint lists or digging into Dataverse, the same logic applies. Once you get the hang of its simple rules, you can tweak your workflows for all sorts of tasks without starting from scratch each time.

Using oData in the Get Items Action: Core Functionality

oData is a widely recognized standard for interacting with data through RESTful APIs. In the context of Power Automate, oData queries allow you to specify exactly which items you want to retrieve from a data source. Think of it as giving clear instructions to a librarian: instead of handing you an entire stack of books, they bring you only the ones you need.

Let’s say you’re managing a SharePoint list filled with employee records—names, departments, hire dates, and more. Without oData queries, the Get Items action might pull every single record, even if you only care about one group. But by adding an oData query, you can narrow it down. For instance, you could filter the list to show only employees in the marketing department. This keeps your workflow focused, processing just the relevant data instead of everything at once.

Why This Matters

Using oData queries in the Get Items action comes with some real advantages:

• Efficiency: By pulling only the data you need, your workflows run faster and avoid unnecessary clutter.
• Precision: You’re working with exactly what’s relevant, making your automations more effective.
• Scalability: Whether it’s a small SharePoint list or a sprawling SQL database, oData queries adapt to the task.

Filtering Different SharePoint Column Types: Detailed Examples

Filtering SharePoint data requires understanding the nuances of different column types, each with specific oData query syntax. Below is a detailed breakdown, supported by examples and best practices:

These examples highlight the flexibility of oData queries, with specific considerations for each column type. For instance, multi-choice columns can be challenging due to their collection nature, and users are advised to verify internal column names in SharePoint settings or via JSON output from the Get Items action.

Using Logical Operators in oData Queries: Building Complex Conditions

oData queries support logical operators to create more sophisticated filters, enhancing the power of data retrieval. The AND operator combines conditions that must all be true, such as Department eq ‘HR’ and Age gt 25, retrieving HR employees over 25. The OR operator, on the other hand, filters items that satisfy at least one condition, like Skills eq ‘Power Automate’ or Skills eq ‘SharePoint, ‘ getting items with either skill.

For complex expressions, grouping with parentheses is essential, as seen in (Department eq ‘IT’ or Department eq ‘Finance’) and Age lt 40, which filters IT or Finance employees under 40. This structure ensures clarity and correct evaluation, particularly in workflows with multiple conditions.

Conclusion: Optimizing Workflows with oData

In conclusion, oData filtering in Power Automate offers a powerful and efficient way to manage SharePoint data, optimizing automated workflows by retrieving only necessary records. Understanding various column types and mastering operators like AND, OR, and parentheses can significantly enhance workflow efficiency. For further assistance, you can get in touch with us by booking yourself a consultation, and we will help you out.