Get Items from a SharePoint List using Power Automate with ODATA Filters

Dominic Liu, 19 November 2020

When you use ‘Get items’ action from Power Automate, it will bring back a list of items from your SharePoint List. This however can take a good amount of time for the Flow to run if you have a large number of items in your SharePoint List. On top of that this action is limited at 5000 items, which means you can only return the first 5000 rows from your SharePoint List.

By using the ODATA filters, it allows you to pull a specific set of items back rather than the entire SharePoint List. In this blog, I prepared a list of New Zealand Schools with School Names, Reference Numbers and their

Main Phone Number,so I could apply a filter to retrieve only those records with a certain Reference Number.

Before you start working on the Flow, you need to find the field name of the ‘Reference Number’ Column. Go to the SharePoint List settings and select the ‘Reference Number’ Column to do this.

image

Once you in the edit column page, you can see the field name in the URL.

image

Now you have the field name, let’s start working on the Flow. I created an Instant flow and added ‘School reference number’ as a user input. Then, in the ‘Get Items’ action, make sure you connect to the SharePoint Site and select your List Name.

In the Advanced options, you can now add the ‘Equals’ function in the ‘Filter Query’ section so that you can find the specific data in the SharePoint List, with the same reference number from the user input. Visit this site to see more OData Filter query functions.

image

This could be truly helpful if you have a SharePoint List with more than 5000 rows as the query will go through the entire list and help you find the specific data that you need.