September 25, 2021

First and foremost, OData or Open Data Protocol dates back to 2007 when Microsoft started it, being standardized later by OASIS (not the band, unfortunately) or the “Organization for the Advancement of Structured Information Standards,” being approved as a standard. When you see committees involved, you know that there’s some serious action happening. Jokes aside, the protocol is super useful because it standardizes the way we can consume RESTful APIs by querying them in a standard way.

We’ll look at examples below, but let’s start with the Power Platform and how it fits there.

The Power Platform and Power Automate especially with it’s actions are no more than abstractions to API calls. This means that we’re using HTTP requests to RESTful APIs without realising it. We see it a little bit when we check the results and see that are returned in a JSON format, so know you know why.

Power Platform, an example

Let’s use Power Automate as an example. Power Automate takes advantage of this in a lot of the actions that it provides. Let’s look at a common action. Get Items from a SharePoint list.

It’s quite simple and useful, but there’s an issue. If the SharePoint list contains a lot of information, then one of two things will happen:

  1. We need to activate pagination to fetch all data.
  2. Power Automate limits the results to 100 so that we don’t have long-running Flows.

Where OData shines

But we don’t always need all the data. It’s much more efficient to use the action with a filter so that the data we receive is only the data we need. This way:

  1. We don’t need to filter in our Flow huge amounts of data.
  2. SharePoint replies faster because there are fewer data to send
  3. SharePoint is much more efficient in filtering data than we are in our Flow, so operations are quite fast as well.

So we need to activate the “advanced options.”

Let’s look at another action. The Excel’s “List rows present in a table.”

As you can see, it’s the same concept. We can limit the number of rows and order the data before we use it. Power Automate does an amazing job in making things consistent with using the concepts for distinct platforms.

The reference

The reference will focus on the “advanced options,” specifically, the “Filter Query” and “Order by.” The protocol allows operators to filter and sort the data as we would use them while consuming any other RESTful APIs.

I’ll show you some complex expressions and break them down so that you can understand each part, where each comes from, and what the effects are.

Please note that we’re using Power Automate as an example, but I’ll show you much more than Power Automate in the reference. Since it’s a protocol, understanding how it works in Power Automate makes it more visual and easier to learn so that you can do more advanced actions later in other parts of the Power Platform.

Finally, keep in mind that this is is not exclusive to the Power Platform. You can use this in any RESTful APIs that support it. So it’s a skill that you learn can apply that anywhere.

You can find the full OData Reference and other references, so please explore them. I’m sure you’ll learn something.

Photo by Brett Jordan on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: