In this Power Automate Tutorial, we will discuss Power Automate get items with examples. We will also see, how to use Power Automate get item with an example.
Also, we will discuss the below topics:
- Power Automate get item
- Power Automate get item id
- Power Automate get item from sharepoint list
- Power Automate get items
- Power Automate get items filter query
- Power Automate get items filter query multiple conidtions
- Power Automate get items fiter query contains
- Power Automate get items count
- Power Automate get items filter query not equal
- Power Automate get items filter query lookup
- Power Automate get items filter query date
- Power Automate get items filter query choice field
- Power Automate get items apply to each
- Power Automate get items Order By
Power Automate get item
In Power Automate, there is an action named ‘Get item‘ which is used to return a single item based on provided ID.
We will find this action under the SharePoint on Power Automate Flow:
After adding this to our flow, it will appear like below:
Here we have to insert the SharePoint site address, List name. On Id, we have to provide an ID according to items in the SharePoint list.
Power Automate get item ID
Here we will see how the Power Automate get item works. For example, we have prepared a SharePoint list named ‘SalesOrder’ like below:
Here, ID is a default column in the SharePoint list. From this list, we want to retrieve the data whose ID is 7. To implement this, we will add a number input in ‘Manually trigger a flow’. Then we will use this triggered ID in
When we will run the flow, it will ask us to input an ID. For example, we will insert 7, then it will show all data of ID 7. As per our SharePoint list, it will show all data of Tab like below:
If we want then we can retrieve only specific data of the provided ID. let’s say we want to retrieve only the Title, Model, and Order Date. For this, we will use a Compose action under the get item:
Now we can see the output will appear like this:
This is how to retrieve a single item from the SharePoint list by using of Power Automate Get item action.
Power Automate get items
In Power Automate, there is an action to get the item from the SharePoint list named Get items. We can find this action under SharePoint(on Power Automate). We will use Get items action to retrieve the list items along with their columns and some other information.
Also, this action returns the filter data based on ODATA filter query specified on the Filter query section.
Let’s have a look at how to find this action on Power Automate. Then we will discuss how to use this action to retrieve data from the SharePoint list by Microsoft flow.
And here it looks like with the advance options:
Let’s discuss the options that are available on that get items action:
- Site Address: Here we have to insert the SharePoint site address.
- List Name: Here we have to give the name of the SharePoint list.
- Limit Entries to Folder: Here we can set the limit of the enteries or actions to a specific folder.
- Filter Query: It gives the most advanced filtering based on set of ODATA filter query of Power Automate SharePoint.
- Top Count: It set the limit number of resulted records. By default, the item limit is 100. If we will work on a large dataset, we need to increase the item limit up to 5000. If we will use the item limit more than 5000 power automate get failed or it will show an error message.
Power Automate get items filter query
In SharePoint’s get items action, there is a filter query option available. This is used to filter the right items from the whole item. An ODATA filter query to restrict the entries returned.
The syntax for the filter query is:
FieldNmae/coloumnName operator fieldvalue
Where the columnName should be the internal names as used by the SharePoint list. We can get this internal field name from the column setting and then we can find it from the URL.
The Operator can be:
|ne||Not equal to|
|ge||Greater than or equal to|
|le||Less than or equal to|
|startswith||Start with the given/specified value|
|endswith||End with the given/specified value|
|substringof(‘abc’,Title)||Search for substring ‘abc’ in field ‘Title’|
Let’s take an example to see, how a filter query works on SharePoint’s ‘get items’ action. For example, we have a list based on SalesOrder.
Now we will filter the data using the filter query. Let’s say it will filter the data that are ordered after 11/8/2021. For this, the query will be:
OrderDate gt '2021-11-08'
Now, we will add a Compose action to retrieve the filtered data:
By running this flow, we can see only those data that are coming after 11/8/2021(i.e. Phone-Apple-11/9/2021 and Headphone-JBL-11/9/2021).
We can see the rest results by clicking on the Next. This is how we can Get items using the OData filter query on power automate from the SharePoint list.
Also, there is an easy way to use this filter query by enabling the experimental features. We can enable this feature from the setting option of the Power Automate.
After saving this, we can see the flow is got modified. Now it is easier to insert the condition according to our needs.
Now we will filter the item based on Company(i.e. OnePlus) using a filter query on ‘get items action’.
Now we will use the action ‘Send an email‘, to check the filtered value:
When we run this flow we can see the filtered value on our mail:
This is how to do filter data using the get items filter query Power Automate.
Power Automate get items filter query multiple conidtions
Here we can add multiple conditions to filter the data. For example, we will add another query that will filter the data based on title and company.
Now we will retrieve the filter data using a Compose action:
Similarly, we can see the filtered output by running the flow:
We can see, it is showing data based on filter query. Let’s take another example, where we will filter the based on Company and Title using OR operator. The query will be like below:
Then we will add a Compose action to retrieve the filter data:
We can see the filtered data on the output of Compose:
We can see more output by clicking on Next on the output of the Compose:
This is how to use multiple conditions on Power Automate Sharepoint get items filter query.
Power Automate get items filter query not equal
Here we will see how a ‘not equal’ operator works on getting items filter query Power Automate. Let’s take an example, to show only those data whose delivery status is not equal to Delivered and Shipped.
To implement this, on filter query we will create a function using ne(not equal to) operator:
Status ne 'Delivered' and Status ne 'Shipped'
To retrieve the filtered data, we will use a Compose action.
Now we can see it only shows the data whose status is not equal to delivered and shipped i.e. only Cancel item.
This is how to work Power Automate get items filter query, not an equal operator.
Power Automate get items fiter query contains
Here, we will see how to get the item from the SharePoint list if that item contains some specific string value.
For example, we want to show items that contain ‘tab’ in the title field. For this, the filter query will be:
Now we will retrieve this filter data by the using Compose action.
In the output, we can see the filter data’s title, model, and its status like below:
As there is one data that contains ‘Tab’, so it is showing one value. This is how to do Power Automate get items filter query contains.
Power Automate get items count
Now we will see how to count the number of items returned by the SharePoint GetItems action in Flow. For example, we want to count the number of ‘Phone’ based on the Title field in our SharePoint list.
To implement this, first, we will filter the specified item (i.e. Phone) from the SharePoint list.
To count that returned items, we will use an expression on composing action:
We can see the total count of filtered items by running this flow:
As there are 2 items(i.e. Phone) in the Title field, so it returned 2. This is how to count items in a SharePoint list using get item action on PowerAutomate flow.
Power Automate get items filter query lookup
Here we will see how to use the SharePoint get items filter query on the Lookup field in a Flow. For example, here we have added a lookup column in our existing SharePoint list.
To use this lookup field here, we have created another list having only country name:
Now we will filter the existing SharePoint list based on the Lookup column. Let’s say, we want to filter the items which are having the value ‘USA’ as lookup.
To implement this, we will insert a filter query:
Lookup/Title eq 'USA'
Here ‘Lookup’ is a lookup column in our existing SharePoint list.
Then we will use a Compose action to show this retrieved value.
We can see it will show only those outputs based on the lookup field value that we mentioned i.e. USA.
We can see the rest output by clicking on Next. This is how to do ODATA Filtering by lookup values on Power Automate flow.
Power Automate get items filter query date
Now we will see how to check the Date in filter query within Power Automate flow. For example, we will check whether the Order Date is less than today(15/11/2021) or not.
For this, we will use an expression on getting items filter query:
OrderDate lt 'formatDateTime(utcNow('yyyy-MM-dd'))'
Also, we will use a compose action to show the returned value:
We can see the output of only those items whose order date is less than today’s date:
We can see the rest output by clicking on Next on the flow. This is how to do the Power Automate filter on SharePoint Date and Time column.
Power Automate get items filter query choice field
Now we will see how to use the get items filter query in the SharePoint choice field on Microsoft flow.
In our existing SharePoint list, we have a choice field named ‘Status‘ having choices Delivered, Cancel, Shipped. For example, we want to filter the items whose status is ‘Cancel‘.
For this, in filter query, we will insert an ODATA filter query like below:
Status eq 'Cancel'
Then we will use a Compose action to retrieve the filter data.
In our SharePoint list, we can see there is only one item whose status is Cancel.
Also, we can see this item in the output of the flow:
This is how to do filtering with the choice field on Power Automate get items.
Power Automate get items apply to each
Now we will see how to avoid the unnecessary loop in our Power Automate flow. Instead of looping, we will use first() that will retrieve the first item from an array or string.
For example, there are items named ‘Phone‘ in the Title of the SharePoint list. But, we want to retrieve the first item.
To implement this, first, we will create a filter query to filer the title based on the name i.e. Phone.
Title eq 'Phone'
To avoid the apply to each loop, we will use the below expression on Compose. That will return the first item from the filter item of the SharePoint list.
Here, first() is Power Automate first function. The body(‘Get_Items’) refers to the name of the SharePoint get items action. The [‘value’] refers to the SharePoint value column which holds all the values of the SharePoint items that don’t need to be changed. The [‘Title’] is the internal name of the column in the SharePoint list.
Now we can see the result without looping by running this flow:
This is how to avoid using Power Automate to get items apply to each.
Power Automate get items Order By
By default, the get items action returns the data in ascending order. But sometimes we need to change the order format in descending order or ascending order according to requirement.
Let’s say, we want to retrieve the data in descending order based on Order date. For this, we have to insert the below formula Order by:
Here, OrderDate is internal column name of the SharePoint list that we want to format in order and desc refers to descending order. Similarly, for ascending order it will asc.
Now, we will use a compose action to retrieve the order data on that:
When we run this flow, we can see the data will come in descending order:
We can check all the retrieved data by clicking on next, that is comes in ascending order based on Order date.
In the time of execution, we can see we got a warning message to inserting the ODATA filter query. We can avoid this warning message by inserting a filter query and then we can order that column.
For example, there are 2 data named ‘Phone’ in title column and we want to retrieve those data in Ascending by Purchase and descending by Company. Then the query will be:
Company asc, Purchase desc
We can see the filtered data will come according to order format. But as we mentioned the purchase order first, so the result will come according to ascending by purchase.
This is how to format the Order in Power Automate ger items.
You may also like the following Power Automate tutorials:
- Power Automate Delete all items in SharePoint list
- Microsoft Flow Example: Copy Files from SharePoint to PC
- Microsoft Flow Example: Automatically create a profile for a new candidate
- Power Automate Parallel Branch with Examples
From this Power Automate Tutorial, we learned all about Power Automate get items action. Also, we discussed the below topics:
- What is Power Automate get items action?
- What is OData filter query in Power Automate SharePoint get items?
- How to use filter query with multiple conidtions in Power Automate get items?
- How to use filter query with not equal operator in Power Automate get items?
- How to work with Power Automate get items fiter query contains?
- How to count items in Power Automate get items?
- How to work with SharePoint lookup column in get items filter query?
- How to filter date in Power Automate get items filte queryr?
- How to filter the Choice field in Power Automate get items filter query?
- How to avoid the unnecessary looping in Power Automate get items?
- How to format the Order in Power Automate ger items?
I am Bijay a Microsoft MVP (8 times – My MVP Profile) in SharePoint and have more than 15 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com