PowerAutomate and SharePoint OData filter queries

PowerAutomate has excellent actions related to SharePoint lists operations like getting, update, and create list items. The PowerAutomate guys introduced 'Filter Query' along with the 'Order by' and 'Top Count' as advanced features to these actions. The 'Filter Query' feature comes handy and can save me overcomplicating the PowerAutomate flow. By overcomplicating, I mean the possibility to pull all the list items and do the filtering inside the flow, which will introduce unnecessary data operations inside the flow, making it slow and complicated.

What is OData?

This question could be relevant for users that do not have a development background and have no clue what OData is. OData or the full name The Open Data Protocol is an HTTP Rest services specification made by Microsoft a long time ago, and the OData is built-in in many of the Office 365 Web services, Microsoft Graph, which is a web service by itself and other.

The problem with OData?

The problem with the OData specification is that it is cumbersome, contains lots of features, and different web services implement it partially. So, one feature of the filters like "substringof('..')" will work for one web service like SharePoint lists and will not work for other services. Because of the different implementation of the standard across Microsoft services, the different Microsoft engineering teams have different manuals on how to work with OData queries. Here is a link to the SharePoint that is not quite rich, and here is the Microsoft Graph team document on the same topic.

Handy queries when working with SharePoint lists

I have some experience with using Odata queries with SharePoint lists, and I would try to cover in this blog. First, it is not a trivial task to filter a SharePoint list or PowerAutomate flow by the Person or Group field. This field is an object containing multiple entities inside, not primitive like string, number, etc. so here are a few recipes on how to filter using SharePoint REST services, then in PowerAutomate flow.

Filter a SharePoint list by Person or Group field using SharePoint rest services

A few examples can come handy. A SharePoint list has a built-in field to track the creator of the item and who last edited the item as well as there might be a custom Person of Group fields. I will use the $select and $expand operators to show how I can get to these fields without any filtering initially.

In the above examples, the PersonOrGroupField stands for a custom Person or Group field created in the list. The Author and Editor fields, as I mentioned, are system fields that come with every SharePoint list. Still, they are handy in filtering data since. Usually, I am interested in creators or editors in multiple business scenarios. Because the PersonOrGroupField is an object, I can access the different entities of the object by specifying PersonOrGroupField/Id, PersonOrGroupField/Name, or PersonOrGroupField/Title. The /Id stands for the SharePoint site user id, and it is number. The /Title stands for the user display name, ex. 'Velin Georgiev' and the /Name stands for I guess the user principal name, which looks like 'i:0#.f|membership|velin.georgiev@email.com' in SharePoint Online.

Filter Person or group field to equal a display name

Here is how I can filter a SharePoint list by user name using the $filter variable and the equal operator. Such a query can be used even from just a browser to pull data. More information on the $filter system query options can be found on the OData official site. Still, as I said before, it is no guarantee that any of the options are implemented for a specific Microsoft 365 or Microsoft Graph service.

Filter Person or group field that contains specific email

Another common scenario is to filter the list where I have only the user email. So then the "substringof('velin.georgiev@email.com',PersonOrGroupField/Name)" comes into play.

Filter a SharePoint list by Person or group field in PowerAutomate

I can apply the same technique in PowerAutomate flow, as can be seen in the picture below. The difference with SharePoint is that in SharePoint, the $expand=PersonOrGroupField operation is must, but somehow the flow handles this internally, and I specify just the filter query.

Here is a picture of PowerAutomate. The flow is moving responses from Microsoft Form into a SharePoint list, and it is using the user email that submitted the form to filter the list items by the user.

Filter list by date field form PowerAutomate and SharePoint REST

Another common scenario is to use filtering over dates in a SharePoint list, and again, the $filter operator can come in handy. The DateTime field also requires a specific format to be passed, so it still might not be trivial to guess the query. Here is how to do it in PowerAutomate first this time.

The SharePoint REST services will be similar, and again, two system DateTime fields are handy for filtering based on business requirements. The Created and Modified field are always present in every SharePoint list. The Created refers to when the list item hm as been created, the Modified date field is there to record the date of the last list item modification.

I assume that the date format of the custom date fields could differ depending on the regional settings of the site so for example US format could be 'yyyy-MM-dd', but Brazil date format might be 'yyyy-dd-MM'

Conclusion

OData filtering is not trivial if the user does not have a programming background, and I experienced it multiple times, working with managers on PowerAutomate flows. SharePoint list is quite a convenient way to store data for business users and perform business automation using PowerAutomate. However, using Odata queries can simplify the PowerAutomate flow, and this is a huge benefit when it comes to troubleshooting and maintenance. Therefore, we as a community have to write down more PowerAutomate OData recipes :)