“The function operator ‘contains’ is not supported or its usage is invalid.” That’s the message SharePoint sends back the first time most of us type contains() into a Get items Filter Query.
In this tutorial, we’ll fix the power automate filter query contains problem with substringof(), match the start of a value with startswith(), and combine both with other conditions.
You’ll also see why “not substringof” fails, how a Filter array handles “does not contain”, and the exact error text each mistake produces. Every count comes from a real flow run on our 620-item list.
Quick answer: SharePoint’s Filter Query doesn’t support contains(). Put substringof('printer',Title) in the Get items Filter Query box instead: the search text goes first, the internal column name second. For “does not contain”, get the items and use a Filter array with @not(contains(toLower(item()?['Title']), 'printer')).
substringof('printer',Title)What You Need Before You Start
We built everything in one instant cloud flow. If the action itself is new to you, start with our walkthrough of the Power Automate Get items action.
- A Power Automate account that can create cloud flows (the SharePoint connector is standard).
- A SharePoint list you can read. Ours is Service Requests on the SPGuides site, with 620 items, a Title column, and Status and Priority choice columns.
- The internal name of the column you’ll search. Title is simply Title; a display name with spaces becomes something like Request_x0020_Type.
About 52 of our titles mention a printer (“Printer jam #1003”, “Printer jam #1015” and so on), which gives us an easy number to check every filter against.
Step 1: See Why contains() Fails in the Filter Query
The Filter Query box isn’t a Power Automate expression. Its text is passed to SharePoint’s REST API as an OData $filter, so only the functions SharePoint understands work there. Our guide to the OData filter query in Power Automate covers the operators in more detail.
To see the failure for yourself, add a Get items action, choose the site and list, open Advanced parameters, and type contains(Title,'printer') in Filter Query.
The action fails with 400 BadRequest. To read the full message, we added a Compose with outputs('Get_items_contains')?['body']?['message'] after it:
Microsoft’s list of supported OData operations for SharePoint names startswith() and substringof() for text, plus eq and ne. It lists endswith, tolower, toupper, substring, trim and concat as not supported, and contains isn’t there at all.
Step 2: Use substringof() in the Get Items Filter Query
substringof() is the old OData way to say “contains”. Here’s how we set it up from scratch:
- Go to make.powerautomate.com, select Create > Instant cloud flow, pick Manually trigger a flow and select Create.
- Select + under the trigger, search for Get items and choose the SharePoint action.
- Pick the Site Address and List Name.
- Open Advanced parameters, select Filter Query and Top Count, then enter
substringof('printer',Title)and 1000. - Select Save, then Test > Manually > Run flow.
To check the result, we added a Compose with length(body('Get_items_substringof')?['value']). It returned 52, the same number our Filter array test found. Our post on how to count items in a SharePoint list shows other ways to get that number.
Mind the argument order
substringof() takes the search text first and the column second: substringof('printer',Title). startswith() is the other way around: startswith(Title,'Printer'). Swapping them is the most common typo we see.
Is substringof() case-sensitive?
Not in our tests. substringof('printer',Title) and substringof('Printer',Title) both returned 52 items, even though every title starts with a capital P. Microsoft doesn’t document this either way, so test with your own data before you rely on it.
Step 3: Match the Start of a Value With startswith()
When the text you need always sits at the beginning, startswith() reads more clearly. startswith(Title,'Printer') returned 52 items, and the lowercase startswith(Title,'printer') also returned 52.
The difference shows up with text in the middle. substringof('jam',Title) found all 52 “Printer jam” items, while startswith(Title,'jam') returned 0. Use substringof() for “anywhere in the text” and startswith() for prefixes such as ticket codes.
Combine substringof() with other conditions
Join conditions with lowercase and / or, just like the examples in our multiple conditions in Power Automate guide:
substringof('printer',Title) and Priority eq 'Low'
substringof('printer',Title) or substringof('VPN',Title)The first returned 52 items and the second 104. Our first attempt used Priority eq ‘High’ and got 0, which looked like a bug until we checked: every printer request in our list is Low priority. A zero isn’t always a broken filter.
Step 4: Filter “Does Not Contain” With a Filter Array
This is where most people get stuck. SharePoint has no “not contains” function, and the obvious workarounds fail. not substringof('printer',Title) returned this:
We also tried substringof('printer',Title) eq false, a fix that shows up in older forum threads. In 2026 it fails too, with “The query is not valid.”
The reliable route is to get the items first and filter them inside the flow. Add a Filter array action, set From to the Get items value, select Edit in advanced mode and enter the condition. Our Power Automate Filter array tutorial walks through the action itself.
@not(contains(toLower(item()?['Title']), 'printer'))On our 620 items, this kept 568, which is exactly 620 minus the 52 printer requests.
The same action handles the positive case when you need it: @contains(toLower(item()?['Title']), 'printer') returned 52, matching substringof().
Keep the Get items Top Count high enough to include every item, because the Filter array only sees what Get items returned.
Filter Query Contains Cheat Sheet
Every row below is a real run against our 620-item list on 10/18/2026. Date filters follow the same rules; see how we filter between dates in Power Automate.
| What you typed | Where | Result |
|---|---|---|
contains(Title,'printer') | Filter Query | 400: The function operator ‘contains’ is not supported or its usage is invalid. |
substringof('printer',Title) | Filter Query | 52 items |
substringof('Printer',Title) | Filter Query | 52 items (not case-sensitive) |
startswith(Title,'Printer') | Filter Query | 52 items |
substringof('jam',Title) / startswith(Title,'jam') | Filter Query | 52 / 0 items |
not substringof('printer',Title) | Filter Query | 400: Fetching items failed. Possible invalid string in filter query. |
substringof('printer',Title) eq false | Filter Query | 400: The query is not valid. |
@contains(toLower(item()?['Title']), 'printer') | Filter array | 52 items |
@not(contains(toLower(item()?['Title']), 'printer')) | Filter array | 568 items |
Common Mistakes to Avoid
- Double quotes.
substringof("printer",Title)failed with: The expression “substringof(“printer”,Title)” is not valid. Creating query failed. Use single quotes. - No quotes at all.
substringof(printer,Title)failed with: The function operator ‘substringof’ is not supported or its usage is invalid. The function is fine; the missing quotes aren’t. - Using the display name. Filter Query needs the internal name. Spaces become _x0020_, so Request Type is Request_x0020_Type.
- Forgetting toLower() in a Filter array.
@contains(item()?['Title'], 'printer')returned 0 because the expression contains() is case-sensitive and our titles say “Printer”. - Trusting the default 100 items. Get items returns 100 items unless you set Top Count (up to 5,000). On lists over 5,000 items, turn on Pagination in the action settings or a filter can come back empty.
- Counting a failed Get items. length() on a failed action fails with InvalidTemplate … The provided value is of type ‘Null’. Check the action status first.
Can You Use contains() Anywhere Else in Power Automate?
Yes. contains() is a normal expression function, so it works in Compose, Condition, Filter array and trigger conditions. It just isn’t an OData function. Our guide to the Power Automate condition for strings uses it in a Condition.
Inside expressions it checks strings and arrays, and Microsoft documents it as case-sensitive. That’s why we wrap the column in toLower() and type the search text in lowercase.
Why Does the Run Say Succeeded When Get Items Failed?
Our test flow set later actions to run after Failed, so the run finished green with red actions inside it. In a production flow, a failed Get items stops the run unless you configure run after. Open each action in the run history before trusting the banner.
Next Steps
Swap contains() for substringof() in your Get items action, run it once, and compare the count with a Filter array on the same list. Then explore more flows in our Power Automate tutorials.
We’d read one of these next if you’re filtering SharePoint data in flows:
Frequently Asked Questions
Why does Power Automate say the function operator ‘contains’ is not supported?
The Get items Filter Query is sent to SharePoint as an OData filter, and SharePoint doesn’t support contains() there. Use substringof(‘text’,Column) instead.
How do I use substringof in a Power Automate filter query?
Type substringof(‘printer’,Title) in the Get items Filter Query box. The search text comes first in single quotes, then the column’s internal name.
What does ‘Fetching items failed. Possible invalid string in filter query’ mean?
SharePoint couldn’t parse the Filter Query. We got it from not substringof(‘printer’,Title). Use a Filter array with not(contains(…)) for ‘does not contain’.
Is the Power Automate filter query contains search case-sensitive?
substringof() and startswith() weren’t case-sensitive in our SharePoint tests. The contains() expression in a Filter array is case-sensitive, so wrap the value in toLower().
Which OData functions does the SharePoint Get items Filter Query support?
startswith(), substringof(), eq, ne, lt, le, gt, ge and date parts like year(). Microsoft lists them in Use OData query operations in SharePoint REST requests.
Hey! I’m Bijay Kumar, founder of SPGuides.com and a Microsoft Business Applications MVP (Power Automate, Power Apps). I launched this site in 2020 because I truly enjoy working with SharePoint, Power Platform, and SharePoint Framework (SPFx), and wanted to share that passion through step-by-step tutorials, guides, and training videos. My mission is to help you learn these technologies so you can utilize SharePoint, enhance productivity, and potentially build business solutions along the way.