the function operator ‘contains’ is not supported or its usage is invalid

Recently, When I was working with Power Automate, I got a task to move the SharePoint list items to another SharePoint list that contains a particular value or a substring.

In this blog post, I will explain the contains in the filter query Power Automate and discuss why an error will be generated when the function operator ‘contains’ is not supported or its usage is invalid.

Power Automate Function ‘Contains’ is Not Supported or its Usage is Invalid.

To know more about the filter query expression contains, go through the points below:

Note:

In the Power Automate Filter query, the expression contains() function is invalid. Instead, we can use the substringof() function to retrieve the list item that contains a particular substring.

The get items filter query contains is not supported for fetching data from the SharePoint list containing a value using Power Automate.

The flow will generate the error like:

This is the clear explanation that the expression is not valid when creating a query that fails in Power Automate.

Power Automate Filter Query Contains

To filter and fetch the data from the SharePoint list using a filter query, check out the below example:

According to the above explanation, as we all know, contains expression is not valid. You can use the substringof() function instead of the contains function in Power Automate to fetch the SharePoint list items that contain a particular value or substring.

Example:

Here, I have a SharePoint list named ‘Power Platform Training‘ with a few columns, as displayed in the screenshot below:

I do also have taken one more empty SharePoint list named ‘Power Automate Training‘ with the below-shown columns:

My requirement is to move items from one SharePoint list [Power Platform Training] to another SharePoint list [Power Automate Training] based on the Course value containing Power Automate.

Here are the steps below:

1. Navigate to  Power Automate Home page -> Select Instant cloud flow -> Select Manually trigger a flow -> Click Create button.

2. Add the Get items flow action and provide parameters such as Site Address, List Name, and Filter Query condition.

Syntax: substringof('value',Column name)

Click Showall to display all the advanced parameters of the get items action.

Add the expression placed below:

Filter Query: substringof('Power Automate',Course)

Where as Course is the Internal name of a SharePoint list field.

3. To move the SharePoint list items to another SharePoint list, add the Create item flow action. Configure the parameters like Site Address and List Name as below:

• Title: Select User Name under Get items of dynamic content.
• Course cost: Choose course cost from dynamic content.
• Course Start Date: Select the course start date from the dynamic content of get items.
• Course End Date: Choose the course end date from get items.
• Course Manager: Select the Manager display name from the dynamic content.

For each loop will be added automatically by taking the body/value of the get items flow action.

4. Then, save and run the flow manually.

5. When the flow runs successfully, you can check that SharePoint list items that contain course value as Power Automate have been added to the other SharePoint list.

This is how to use contains in filter query of get items Power Automate to filter and retrieve the details from the SharePoint list.

Conclusion

I hope this tutorial has given you an idea of how to use Power Automate to get items filter query contains in Power Automate. Also, I explained how to fix the error “the function operator ‘contains’ is not supported or its usage is invalid.“.

You may also like:

• The execution of template action ‘Switch’ failed
• Power Automate Trigger Conditions
• When a New Email Arrives Subject Filter in Power Automate
• Power Automate Convert String to Float