Power Automate get items examples

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:

Power Automate get item
Power Automate get item

After adding this to our flow, it will appear like below:

Get item action on Power Automate
Get item action on Power Automate

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.

Read Power Automate save email attachment to SharePoint

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:

Retrieving Single Item from SharePoint List in Microsoft Flow
Retrieving Single Item from SharePoint List in Microsoft Flow

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

Retrieving Single Item from SharePoint List in Power Automate
Retrieving Single Item from SharePoint List in Power Automate

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:

Power Automate get item id
Power Automate get item id

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:

Retrieving Single Item from SharePoint List in Power Automate Get item
Retrieving Single Item from SharePoint List in Power Automate Get item

Now we can see the output will appear like this:

Power Automate get item from sharepoint list
Power Automate get an item from the SharePoint list

This is how to retrieve a single item from the SharePoint list by using of Power Automate Get item action.

Read Power Automate If expression with examples

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.

Power Automate get items
Power Automate get items

And here it looks like with the advance options:

Get items on Power Automate
Get items on Power Automate

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 action
Power Automate get items action

Read Power Automate vs UiPath

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:

OperatorsDescription
eqEqual to
neNot equal to
gtGreater than
ltLess than
geGreater than or equal to
leLess than or equal to
andAnd
orOr
startswithStart with the given/specified value
endswithEnd 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.

Power Automate get items filter query
Power Automate get items filter query

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'
Power Automate get items OData filter query
Power Automate get items OData filter query

Now, we will add a Compose action to retrieve the filtered data:

Power Automate SharePoint get items OData filter query
Power Automate SharePoint get items OData filter query

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).

Get items OData filter query power automate SharePoint list
Get items OData filter query power automate SharePoint list

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.

Power Automate get items filter query Experimental features
Power Automate get items filter query Experimental features

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’.

get items filter query on Power Automate
get items filter query on Power Automate

Now we will use the action ‘Send an email‘, to check the filtered value:

filter query in SharePoint Get items in Power Automate
filter query in SharePoint Get items in Power Automate

When we run this flow we can see the filtered value on our mail:

filter query in SharePoint Get items action in Power Automate
filter query in SharePoint Get items to action in Power Automate

This is how to do filter data using the get items filter query Power Automate.

Read Power Automate conversion of string

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.

How to filter SharePoint get items on Power Automate
How to filter SharePoint get items on Power Automate

Now we will retrieve the filter data using a Compose action:

Get items filter query on SharePoint Power Automate
Get items filter query on SharePoint Power Automate

Similarly, we can see the filtered output by running the flow:

Get items filter query on SharePoint Power Automate
Get items filter query on SharePoint Power Automate

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:

Power Automate get items filter query multiple conditions
Power Automate get items filter query multiple conditions

Then we will add a Compose action to retrieve the filter data:

get items filter query multiple conditions on SharePoint Power Automate
get items filter query multiple conditions on SharePoint Power Automate

We can see the filtered data on the output of Compose:

Power Automate Sharepoint get items filter query multiple conditions
Power Automate SharePoint get items filter query multiple conditions

We can see more output by clicking on Next on the output of the Compose:

Power Automate Sharepoint get items filter query multiple conditions
Power Automate Sharepoint get items filter query multiple conditions

This is how to use multiple conditions on Power Automate Sharepoint get items filter query.

Read Power Automate Number Format

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'
Power Automate get items filter query not equal
Power Automate get items filter query not equal

To retrieve the filtered data, we will use a Compose action.

Power Automate SharePoint get items filter query not equal operator
Power Automate SharePoint get items filter query, not equal operator

Now we can see it only shows the data whose status is not equal to delivered and shipped i.e. only Cancel item.

OData Filter Query for Not equal in Power Automate
OData Filter Query for Not equal in Power Automate

This is how to work Power Automate get items filter query, not an equal operator.

Read Power Automate Initialize Variable

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:

substringof('Tab', Title)
Power Automate get items filter query contains
Power Automate get items filter query contains

Now we will retrieve this filter data by the using Compose action.

Power Automate Get Items Query filter for contains
Power Automate Get Items Query filter for contains

In the output, we can see the filter data’s title, model, and its status like below:

OData Filter Query in SharePoint Get items in flow
OData Filter Query in SharePoint Get items in Microsoft flow

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.

Read Power Automate convert time zone

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.

Power Automate get items count
Power Automate get items count

To count that returned items, we will use an expression on composing action:

length(outputs('Get_items')?['body/value'])
How to count number of items returned by SharePoint GetItems action in Flow
How to count the number of items returned by SharePoint GetItems action in Flow

We can see the total count of filtered items by running this flow:

Flow to count items in a SharePoint List
Flow to count items in a SharePoint List

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.

Read Power Automate Concatenate String

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.

Power Automate get items filter query lookup
Power Automate get items filter query lookup

To use this lookup field here, we have created another list having only country name:

ODATA Filter queries on LookUp columns from a SP List
ODATA Filter queries on LookUp columns from an SP List

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.

ODATA Filtering by lookup values on Power Automate flow
ODATA Filtering by lookup values on Power Automate flow

Then we will use a Compose action to show this retrieved value.

ODATA Filter Query on lookup field in Get Items (SharePoint)
ODATA Filter Query on lookup field in getting Items (SharePoint)

We can see it will show only those outputs based on the lookup field value that we mentioned i.e. USA.

Power Automate filter on SharePoint Lookup column
Power Automate filter on SharePoint Lookup column

We can see the rest output by clicking on Next. This is how to do ODATA Filtering by lookup values on Power Automate flow.

Read Leave Request Approval Flow using Power Automate or Microsoft 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:

Power Automate get items filter query date
Power Automate get items filter query date

We can see the output of only those items whose order date is less than today’s date:

Power Automate filter on SharePoint Date and Time column
Power Automate filter on SharePoint Date and Time column

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.

Read How to convert decimal to whole number in Power Automate

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.

Power Automate get items filter query choice field
Power Automate get items filter query choice field

In our SharePoint list, we can see there is only one item whose status is Cancel.

Get items Filter query for choice field in Power Automate
Get items Filter query for the choice field in Power Automate

Also, we can see this item in the output of the flow:

SharePoint Get Items filter with Choice field on Power Automate
SharePoint Get Items filter with Choice field on Power Automate

This is how to do filtering with the choice field on Power Automate get items.

Read How to move files from OneDrive to SharePoint using Power Automate

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.

first(body('Get_Items')['value'])['Title']

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.

Power Automate get items apply to each
Power Automate get items apply to each

Now we can see the result without looping by running this flow:

Apply to Each Control Action in Power Automate get items
Apply to Each Control Action in Power Automate get items

This is how to avoid using Power Automate to get items apply to each.

Read Run Flow on a Schedule in Power Automate

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:

OrderDate desc

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.

Power Automate get items Order by
Power Automate get items Order by

Now, we will use a compose action to retrieve the order data on that:

How to order SharePoint items returned by Get items in Power Automate
How to order SharePoint items returned by Get items in Power Automate

When we run this flow, we can see the data will come in descending order:

Power Automate Get items action Order By
Power Automate Get items action Order By

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
Get items Order by in Power Automate
Get items Order by in Power Automate

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.

Get items Order by in Microsoft flow
Get items Order by in Microsoft flow

This is how to format the Order in Power Automate ger items.

You may also like the following Power Automate tutorials:

Conclusion

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?
  • Could you make a tutorial on how to get items from a certain action/connector? I need to get all values from a ‘Get item’ from sharepoint using an expression

  • Great article, thank you! Any insights as to why the Top Count description says “default = All” when default actually = 100?

  • You don’t mention how you get those values out of the “Compose” object(s)? How do you reference them outside of the “Apply to each” once it’s finished? I was thinking it would be some sort of expression, like
    body(‘Apply_to_each’)?[‘value’]?[0]?[‘ID’]
    ???
    It would have been nice to have gone into that, at the end. But I guess you’re supposed to do everything you want with them on-the-fly. So I figured out that your loop will already have the “value”:
    outputs(‘Get_items’)?[‘body/value’]
    and then, if you want a particular field off of that, like ID, it becomes
    items(‘Apply_to_each’)?[‘ID’]
    You should have mentioned this.

  • >