Starts with function in OData Filter

An OData filter query can optimize your flows in Power Automate  dramatically.

First collecting all items and then use conditional logic to do what you  want to do is just not good enough.

SharePoint Get Items action

Before reading this post you might also want to have a look at the new still in preview version of filter queries.

Easier way to manage Filter Queries using the experimental features

When you develop flows for SharePoint in Microsoft Flow you will find yourself many times dealing with list items. Adding items, removing items, updating item it is all easy. One of the trickier things is the SharePoint Get Items action. Quite quickly you will find that you need to understand OData Filter query option.

Get Items Action with OData Filter Query in Power Automate

I will start by having a look at doing things the wrong way. Yes, I like the showing you the wrong way as much as I like showing you the right way.

The Wrong Way

When you get list items do you find that you are getting too many items back and that you need to use conditions or other options within Flow to select the right items?

You will probably find that your flow looks a bit like this. The general structure to look out for is a Condition as the first step inside an Apply to each control while one of the branches of the control is empty.

Get Items followed by an Apply to each  with a condition in it in a Flow

In general this means that you simply collected to many items and your now looping through too many items. This is not a good idea.

The Better Way with an OData filter query

Within the SharePoint Get items action there is a Filter Query available. This Filter Query can be used to select the right items.

The problem with this Filter query however is that it isn’t immediately clear what the syntax is. The Tooltip helps a little bit:

An ODATA filter query to restrict the entries returned (e.g. stringColumn eq ‘string’ OR numberColumn lt 123).

For non-developers/citizen developer this might not immediately help. The other problem is that this syntax is actually wrong!

recently I tried the following and it didn’t work!

stringColumn eq 'string' AND numberColumn lt 123

It didn’t work until I changed it to the following filter query

(stringColumn eq 'string') and (numberColumn lt 123)

I’m going to start by having a look at the syntax.


The general syntax of a simple query is:

fieldname operation value

The field names that are used are the internal field names as used by SharePoint. These internal field names can be found within column settings in SharePoint. Simply go to the settings for the column and in the URL you will find the field name.

The operations can eq, be lt, gt, ge, le, ne  (Equal to, Less Than, Greater Than, Greater than or Equal to, Less than or Equal to, No Equal to).

This makes it easy to compare a field value to an actual value. however you might find that you need to query multiple field. The easiest approach is the use of the and or or operations.

Finally the value is the value that you are comparing the field name with. Please note that you will need to use single quotes (‘) around the values. For numbers Flow is happy to accept both with and without quotes, however for text values they are required. Therefore you might as well always use quotes.

Available Functions

Now that I’ve mentioned functions in the Query Filter it might be useful to have a look at the available options and potential ways of using them. The following functions are available within the query filters:

  • endswith
  • startswith
  • substringof
  • length
  • day
  • year
  • hour
  • minute
  • second

Some of these are more obvious than others. Time to look at some examples.



endswith(MyField, 'test')

The above example will select all items where MyField ends with test.



startswith(MyField, 'test')

The above example will select all items where MyField starts with test.

SharePoint Get Items action showing startswith(Title, test)


The substringof function is on that you might get wrong the first time you use it. Especially when you are familiar with the starts with or ends with functions. also the documentation link  that you might find when you google is wrong. The better document to look at is Use OData query operations in SharePoint REST requests, although that page doesn’t seem to list all; available functions.

Get Items showing substring of function

Note that in the Filter Query you first have to supply the text you are looking for followed by the field value. Therefore the right example is:



The length function is not supported.

day, year, hour, minute, second

When you work with dates querying by day can be useful. For example when you want to find all items that were modified on the first day of the month you should be able to use the day function.

Get Items showing date check on created date

However I couldn’t get the date and time functions to work in Power Automate. The only way to filter by dates is the simpler option of comparing the date field with a specified date.

using something like the following as a query should work:

Created gt '2018-11-25'

But I have seen the above fail when the wrong format for the date is used. So be careful.

Then I created a new list and created a new list with dates and it worked as shown below. Both gt and eq worked for date time fields that included and the ones that didn’t include the time.

OData Filter query in SharePoint Get items in Power Automate Microsoft Flow, Microsoft Power Automate image 57

So the last example shows us how to filter by a date, but what if there are empty date fields. Can we filter those out?

Yes we can, please see my post on how to filter out empty dates.

Filter queries in the List records action in the Common Data Services connector

Shed some light on arrays

Filter data in an array

Avatar for Pieter Veenstra

By Pieter Veenstra

Business Applications Microsoft MVP working as a Principal Architect at HybrIT Services Ltd. You can contact me using

59 thoughts on “OData Filter query in SharePoint Get items in Power Automate”
  1. how would we test multiple fields please?

    We need to filter on two columns called T-Code and BoxNo and have tried

    BoxNo eq @{triggerBody()?[‘BoxNo’]} && T-Code eq @{triggerBody()?[‘T_x002d_Code’]}

    but we get an error that the expression is not valid

      1. Hi It seems that it is not working in the Flow it throws an error like below, stating that only one eq s supported any ideas on it ?

        Error is as below:

        Invalid filter clause: unsupported operation. Only single ‘eq’, ‘ne’, ‘contains’, ‘startswith’ or ‘endswith’ is currently supported.

  2. This is very helpful. I am still a bit stuck however, trying filter items only older than today so I can update overdue entries.

    I have a Date column named DueDate. Trying to get items where the value of DueDate is older than today. I tried with no success:

    DueDate LT Today()

    Any suggestions?

      1. Brilliant, this worked perfectly!

        I was stuck trying to get one of my PowerApp galleries to only display SP list items older than today but kept running into delegation limitations. Needed the SP list to be filterable by a Single Line Text field. Now I can run a nightly flow that gets items in the list which are both marked “Active” and have a DueDate older than today = have Flow change the text field entry to “Old”, resulting with my PowerApp now being able to filter it’s gallery items to only display active items properly without delegation problems. (Bit of a crazy workaround, but MS just doesn’t seem to like PowerApps filtering SP lists on anything but the Single Line Text field for large lists)

  3. Hi! I’m copying from one SharePoint list to another, trying to filter based on a Manual Trigger in which they’re selecting a value of an existing field. So essentially I want the query filter to get the items where field “Budget Field” is equal to the value selected in the drop down of the Manual Flow Trigger. Is this possible?

    1. Hi Jenny,

      Yes that should be possible the query is probably something like

      Budget_x0020_Field eq ”

      Not that the field name is the internal name of the field ( in list settings click on the field and get this form the url)
      The calue that you are testing for has to be inside single quotes ( ‘ )

      1. Thank you! The trick was this: Budgeting_x0020_Phase eq ‘@{triggerBody()[‘text’]}’. Everything after the @ is inserted automatically when you select the text field from Dynamic Content – it just needs to be in single quotes.

  4. How can we test a choice field?

    In our list we have a field called Status, with possible values such as Approved/Pending/Expired etc

    But in our Flow we only see “Status Value” as an option, so when we do Status Value eq ‘Pending’ there is an error?

    1. Hi Philip,

      Do you have details on the error?

      I’m guessing that the space in Status Value is causing the problem.Status Value is likely the display name of the field rather than the internal name of the field.

      Can you go to the list settings and click on the name of the field. Then as part of the url you will see the internal name of the field.

      My guess is that this should be just Status
      So your query should be:
      Status eq ‘Pending’

  5. Hi,
    I am using an OData Filter on an excel document that I have stored in Sharepoint.
    When I use “substringof” filter, substringof(@{variables(‘entityValue’)},Description), where “Description” is the name of the lookup column in the excel sheet, i receive an error:

    An unknown function with name ‘substringof’ was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.

    Is “substringof” filter supported by excel online connector, or should I use “contains”?
    I tried using “contains”, but I think I am not using the right syntax.

    Can you please help.

      1. Thanks Pieter,

        Can you let me know the syntax of using “contains”.
        I searched a lot but not able to find an example where “contains” is used to filter.


  6. Hi Pieter,

    My goal is to bulk update/create rows in a main Excel sheet which current has more than 15,000 rows, then I have another separate sheet – update to put all rows need to update or create.

    So I list the rows of Update first, then list the main sheet using Key eq ‘Key’, but it will auto apply to all, then I can’t apply the bulk update/create method from John Liu’s post. But I can’t find the rows to update at all if trying to list all rows of the main. Is there a better way? Many thanks, Vera

  7. Hello Pieter,

    I found a way to deal with dates in the filtering query:
    You can use some part hard coded in the query, and combine it with an expression. The result will be something like ReqDate lt datetime’@{addDays(utcNow(), -365)}’.

    In the user interface for “Get items” just after writing ReqDate lt datetime’ I inserted the expression addDays(utcNow(), -365).

    So we can do this in two ways:

    1. Write in the query directly using the dynamic part inside @{}.


    2. Write part of the code in the query and combine it with an expression using the user interface.

    I hope this will be useful!

    Best regards,

    Camilo Echavarria.

  8. I have a huge dataset which is approx 0.5 million where I am trying to put a condition on datetime column (stream_load_datetime) which I have in a table to limit it only for a day of data in the filter query while getting rows in flows from Azure SQl data warehouse. Tried to add the following filters but didn’t work:

    1. stream_load_datetime gt ‘2020-01-07’
    2. filter=year(stream_load_datetime) eq 2020
    3. stream_load_datetime gt ‘addDays(utcnow(‘yyyy-MM-ddTHH:mm:ssZ’),-1)’

    Can anyone please help me in finding the correct filter for the same.


  9. Tried using this syntax: stream_load_datetime gt ‘2020-01-07’

    Got the following error:

    Syntax error: character ‘‘’ is not valid at position 24 in ‘stream_load_datetime gt ‘2020-01-07’’.
    inner exception: Syntax error: character ‘‘’ is not valid at position 24 in ‘stream_load_datetime gt ‘2020-01-07’’.
    clientRequestId: 4909c1a3-e205-4fc4-9403-93409771530f

  10. I ran the following syntax: stream_load_Datetime gt ‘2020-01-07’
    Now the error is: Http request failed: the content was not a valid JSON.

    1. I just tried this in my environment and

      Modified gt ‘2018-10-01’

      did work.

      When I renamed used Modified_123 (this doesn’t exist) instead of Modified

      “status”: 400,
      “message”: “Column ‘Modified_123’ does not exist. It may have been deleted by another user.\r\nclientRequestId: 633139a7-5e2e-46e4-96bc-80d503dce313\r\nserviceRequestId: 633139a7-5e2e-46e4-96bc-80d503dce313”

      So then I tried the following:

      Modified gt ‘2020-01-07’

      and this worked again.

      So it looks like it has something to do with your field configuration.

      Can you create a new date field call it something simple as MyDate (Anything without spaces) and then try again.

  11. I have a column named “EventID” in a SharePoint list. But when in the OData filetr query I am using EventID eq ‘Id’ (note: this Id is the event Id in outlook event) I am getting below error
    The expression \”EventID eq ‘AAMkAGQ0M2Y0YzhkLTAxMzktNGU3Ny04MjcxLTY0NDE1NGYyODc5MQBGAAAAAADlJ_DHdsrtS7Mu4R5VMJ9aBwDQYdHYDxEPT4DQiZSgsjDqAAAAAAENAADQYdHYDxEPT4DQiZSgsjDqAAAZqMmjAAA=\” is not valid. Creating query failed.\r\nclientRequestId: 2c53b966-f454-4672-ba72-a661db4c2518\r\nserviceRequestId: 2c53b966-f454-4672-ba72-a661db4c2518″

    I have also tried with Initialize Variable but got the same error. But if I put the Id value like below way
    EventID eq ‘AAMkAGQ0M2Y0YzhkLTAxMzktNGU3Ny04MjcxLTY0NDE1NGYyODc5MQBGAAAAAADlJ_DHdsrtS7Mu4R5VMJ9aBwDQYdHYDxEPT4DQiZSgsjDqAAAAAAENAADQYdHYDxEPT4DQiZSgsjDqAAAZqMmjAAA=’ then it is working fine. Can you please help me how I can solve this problem?

  12. Can you please tell me how to filter out anything that contains a certain substring. For example I am using Get Items from SharePoint Online and want to filter out any items where the Owner field contains ‘Error’

    I see I could use substringof(‘Error’,Owner) to select them, but how do I select everything except them?

  13. Hi Pieter, it’s always great to read your posts. Thank you :-). I wonder if you have any solution for the following:

    I have a person or group column in a SPO list which can hold more than one person.

    I need to either perform an ODATA filter in the Get Items action or in a later FILTER action on the column to determine if a specific person is included in the column and return the list item(s) if they are.

    Any ideas? I’ve spent hours searching and testing various methods but am getting nowhere.

    I tried using contains on a single person field too, but always seem to get an empty array back (tried email address and part of name; I even tried a ‘.’).

    Thank you again 🙂

    1. You could use a select action. Feed it with the peoplefield.

      Now you will have an array of email addresses. Use the join function in a compose action to create a ; separated list. Now contains will work on that string.

      1. Thanks for your article. I’m not sure I understand your suggestion for Cologne Claret’s issue. If I want to filter a SharePoint list by a multi person field that CONTAINS a specific person, among other people, how to do that with the ODATA filter? Your suggestion seems to be to look at each item individually. But that means I have to loop through all list items. I just want to return the list items where the person is contained in the multi person field. Is that possible?

      2. There isn’t a simple ODATA filter for multi people fields. Hence the collect the data and then process it afterwards approach. It is indeed very disappointing that things like this are not available. But then the underlying REST API/Graph API fitlers don;’t support filtering on related items. When you see people as a related list/table that direct filtering is not possible.

  14. I’m not getting the expected results when trying to filter by both an eq and ne as below. I know there is 1 result, but it returns empty.

    Project_Status eq ‘Archived’ and Status ne ‘Archived’

    I’ve also tried wrapping them in () with no effect.

    (Project_Status eq ‘Archived’) and (Status ne ‘Archived’)

    Is this not supported?

  15. Hi Pieter,
    How can I makes multiple groups of conditions, like if (this and this) or (this and this)?

  16. Hello! Great post. I am attempting to use Get Items – Filter Query to find part of a subject line. In the subject line I have a calculated field (UNIQUE ID) which brings in last name and created date.

    UNIQUE ID: Bailey-2021-11-17-0735
    Subject line (when it is received) : [External Email]Re: Bailey-2021-11-17-0735

    I have tried many attempts but my logic is this.
    substringof(‘SUBJECT’, UNIQUE_x0020_ID)

    Just cannot get this thing to lay down. Help! 🙂 Thank you in advance.

    1. Hi Jordan,

      Your example of:

      substringof(‘SUBJECT’, UNIQUE_x0020_ID)

      Will test if the letters SUBJECT are found in the field with the internal name UNIQUE_x0020_ID. I’m suspecting that you are trying to query the content of the field UNIQUE_x0020_ID with the content of the field subject. That isn’t possible. You can only query by values not by fields.

      1. Thank you Pieter. Yep I was trying to query the content of the dynamic expression ‘SUBJECT’ from my outlook subject line to the unique id in my sharepoint list. Bummer. Thank you so much for your quick response. You now have a dedicated supporter/follower.


  17. Hi.
    Need to query on a SQL column
    Column name is ‘Material’
    It contains all the Material number information

    If i want to do a partial search in SQL DB we use the LIKE operator. Can we have a similar search in Power Automate filter.
    If i search for 346 it should return the second and third rows.
    I have tried using “Contains” and substring(‘value’, Column) and both of them didn’t work. Can anyone suggest any alternative.


Leave a Reply

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

Discover more from SharePains by Microsoft MVP Pieter Veenstra

Subscribe now to keep reading and get access to the full archive.

Continue Reading

%d bloggers like this: