Get your SharePoint data the right way

Hey everyone,

this series will try to tackle all the little hiccups that many of you out there encounter while using the Power Platform. Today we will talk about how you can use the SharePoint Filter Query with the Get Items action in Power Automate and show the "old" manual filtering and the new and modern, but still an Experimental Feature, Query Filter.

Using the Filter Query in Power Automate requires the next step of knowledge around not only SharePoint but APIs in general. When you use the Get Items action, what happens in the background is that Power Automate will do a Get request to SharePoint using your credentials.

If you want to know what credentials Power Automate is using to get you the SharePoint items you request, you can do that by selecting the ellipsis at the top right corner (1) of the action item. You will find the connections at the bottom of the tab that pops out (2).

APIs are usually limited to the amount of data they can transfer after each request. SharePoint has a threshold of 5000 items and if you are calling a SharePoint List that has more than that, you will end up missing data. For that reason it is suggested to use the Filter Query.

In Power Automate until now there was a specific syntax you had to use to be able to use the Filter Query. Here is a clean list of them that I found in a blog post from DIY365 (https://diyd365.com/2019/11/20/every-power-automate-ms-flow-filter-query-you-ever-wanted-to-know-as-a-functional-consultant/) if you are still using the "old" filtering method:

Operator            Description
---------------------------------

eq             =    Equal to
ne             =    Not Equal to
contains       =    contains
not contains   =    Does not contain
gt             =    Greater than
lt             =    Less than
ge             =    Greater than or equal to
le             =    Less than or equal to
and            =    And
or             =    Or
startswith     =    Starts with the specified value
endswith       =    Ends with the specified value

One of the most frequent errors people encounter is when they write the name of the column/s they want to use the Filter on. Power Automate does not see the same column name we see when we go to our SharePoint List. Reason for that is because what we see on SharePoint is the view name, and what Power Automate sees is the back-end name. On the next two screenshots you can see what our view (1) of the column name is and what Power Automate sees (2).

If you were to write Project Name eq "Contoso", even if you have that project name in your SharePoint List, it will return an error because Power Automate cannot find a column called Project Name. Instead what it sees is Project_x0020_Name which is a weird way of replacing a backspace but hey, who am I to judge? Now you might be asking ok, but where do I find the back-end name of the column I want to filter on? Great question, for that I have prepared a GIFt for you:

So if you are still using the old Filter Query, make sure you grab the right column name and use the appropriate filter syntax. For those who like to experiment and would like to use the Experimental features, take a look below if you want to learn how to do that. Keep in mind that using Experimental features in a production environment is not suggested!

This makes everything much easier since you do not have to worry anymore if you have the right column name or not and if you are using the right filter syntax. Power Automate takes care of that for you for an even better user experience. If you want to take a look at the video I did around the Experimental Features, take a look at the one below.

Thank you for your time!

#SharePointOnline #PowerAutomate #PowerPlatform #FilterQuery #Microsoft