Are you aware of using an OData filter query startswith in a Power Automate flow? In this tutorial, I will demonstrate how to use Power Automate SharePoint ‘Get items’ filter query startswith condition inside a flow.
To fetch items from a SharePoint list using Power Automate Odata Filter Query Startswith, a user can apply a filter query condition in a Power Automate ‘Get items’ flow action to filter out the data. Example: startswith(Title, ‘SharePoint’) and CourseDelivery eq ‘Fulltime’.
Power Automate Filter Query Startswith String
Microsoft provides filter query parameters in the ‘Get items‘ and ‘Get rows‘ actions where it can filter and limit the data from a data source like a SharePoint list or SharePoint document library.
Power Automate string function i.e.’ starts with ‘filters the rows or items that have a value starting with a given string in the filter query of get items action.
To get the items from a data source, we can also use the startsWith() function instead of a filter query.
Follow the below instructions or steps to build a Power Automate flow:
- Create an Instant cloud flow in Power Automate.
- Add Get items action under the trigger.
- After that, add a create HTML table.
- Save and Test the Power Automate flow.
- Output of the Filter Query.
To do that, I will use a SharePoint list named ‘Course Enrollment Form‘ with ‘Course to Enroll’ as a Single line of text (Title column) datatype as displayed below.
Recently, I was required to filter out the number of persons who have enrolled for the course ‘SharePoint’ along with course delivery being equal to ‘Fulltime‘.
- To create an instant cloud flow, log into Power Automate with your Microsoft credentials.
- Then, click on + Create and select Instant cloud flow.
- In the next window, select “Manually trigger a flow” and click on Create.
- In a Power Automate flow studio, add the ‘Get items’ flow action under the trigger and provide the below-given properties:
- Site address: Set a specific SharePoint site from a drop-down.
- List Name: Select a SharePoint list name from the drop-down.
- Filter Query: Provide the filter query syntax like below based on SharePoint list columns.
startswith(Title,'SharePoint') and CourseDelivery eq 'Fulltime'
Important:Power Automate filter query condition ‘notstartswith‘ is restrictive and not supported to filter out the data. And, this condition will get the error as ‘Fetching items failed. Possible invalid string in filter query’.
Example: not(startswith(My_x0020_Text_x0020_Column,’myText’)) – Not valid
notstartsWith(My_x0020_Text_x0020_Column,’myText’) – doesn’t work – Not valid
Where ‘Course Enrolled’ is the Title column and ‘eq’ means equals to in the Power Automate filter query syntax like the below screenshot.
- In the next step, add Create HTML table and set the below details in it:
- From: Take value from dynamic content.
- Columns: Select a Custom table from the drop-down.
- Then, add custom table values manually, as shown in the table:
Date of Enrolling
|Select values from the dynamic content of a Get items output.|
- Now, the flow is ready to save and test. Click on Save and Test Manually.
- Then the flow will run successfully and display a notification as ‘ Your flow ran successfully ‘ as shown below:
- The Body of an HTML table lists items that satisfy the condition as the course starts with ‘SharePoint’ along with the course delivery ‘ Fulltime’.
This is how to work with the Power Automate OData filter query startswith function.
I hope I have provided clear steps on how to use Power Automate SharePoint get items filter query starts with condition. Through this Power Automate tutorial, you will be able to understand the functionality of the Odata filter query inside a flow.
You may also like:
Preeti Sahu is an expert in Power Apps and has more than 6 years of experience working with SharePoint and the Power Platform. As a Power Platform expert for Power BI, Power Apps, Power Automate, Power Virtual Agents, and Power Pages, she is currently employed with TSinfo Technologies. She is the author of the book Microsoft Power Platform A Deep Dive. She also made a big technical contribution to SharePointDotNet.com in the form of articles on the Power Platform. She enjoys traveling and spending time with her family in her spare time.