ODATA Queries in PowerAutomate made easy

Wow thats a lot of data. Never fear, ODATA is here!

Hi, and welcome to another tutorial from myself and the team here at NextStep Creations. Today we’re going to be talking about ODATA queries.

ODATA queries are a fantastic way to filter or organize your data in PowerAutomate. They can be used for many different sources, such as:

  • SharePoint
  • SQL
  • API calls
  • Dynamics

In this guide, we’ll run through how to use OData queries and some example use cases.

Basic ODATA queries

The general syntax of an ODATA query is:

fieldname operation value

The fieldname is the column or field that you wish to search (something like customerName, customerType, mobileNumber)/

The operation is one of the following:

  • eq: Equal
  • gt: Greater Than
  • lt: Less Than
  • ge: Greater Than or Equal To
  • le: Less Than or Equal To
  • ne Not Equal

You can also stack more than one operator/query, by using “AND” or “OR” to specify multiple conditions.

And finally, value is what you are comparing your column to. This can be a string, a date, an integer, a floating point number, or even a boolean (true/false). 

Note: Flow requires single quote (‘ ‘) around text in the value section – customer eq ‘Jacob’

Using an ODATA Query

Alright, lets get into it. First, create a brand new flow. Just use the “Manually trigger a flow” start point for now.

In this example, I’m going to add a “Get Items” step, that will pull all my items from a SharePoint list. I’ll run the Flow, so we can see what (unfiltered) data we get:

No ODATA query

There’s a fair few lines of data there. I don’t need all of them…..

Let’s say I only want the items from the list where the title is “odata”. Go back and edit your Flow, and you’ll see an option in the Get Items step called “Filter Query”. In that box, put the following query:

Title eq ‘odata’

Save it, and run your flow:

ODATA Simple Query

Voila! That’s a much smaller list, with only the matching items in it.

Think of the possibilities….no more having to loop across all of your data trying to find a certain value., you can just make sure you only have items that match your criteria in the first place.

Multiple ODATA Queries

The fun doesn’t stop there! Let’s say I need to find items with the title of “odata”, and I also need to find the one with the ID of 11.

Edit your Flow again, and change your filter query to:

Title eq ‘odata’ and ID eq 11

Time to run that flow! Let’s see what we get:

ODATA Double Filter

Awesome! One item and one item only. That’s a nice way to filter out data before we even operate on it.

Wrap up & thoughts

This is a basic intro to using filter queries in PowerAutomate. Stay tuned for a deeper dive blog into some more of the intricate functionality.

Reach out to us on Twitter at NextStep Aus, we love to see what you make out of our tutorials!

Leave a Reply

Your email address will not be published. Required fields are marked *