Filter Query

Filter Query in SharePoint Get Items in Power Automate

Have you tried the experimental version of Filter query in the SharePoint Get Items action in Power automate yet?

Filter Query in Get Items

In this post I will look at the experimental Filter Query feature and how you can make use of this in your production environment. I hear you! Experimental features in production? Of course not!

A while back I wrote a post about filter queries in the Get Items action. It is one of the most popular posts on my blog with about 400 views every day. this shows how many people struggle to get it right.

The old Filter Query option

Microsoft have realized this and they are working on an easier to use filter query settings. You can already use this by enabling the experimental features.

You can enable these from the settings in Power Automate. Just click on the View all Power Automate settings.

Filter Query in SharePoint Get Items in Power Automate Microsoft Power Automate View all Power Automate settings 1

And you will get the following dialog and all you have to do is toggle the experimental features on.

Filter Query in SharePoint Get Items in Power Automate Microsoft Power Automate Experimental Features 1

Before you do this, do make sure that you have saved your flow first!

Then you can now reload your flow or create a new one and you will notice straight away that the get items has a nicer looking Filter Query there.

The New experimental filter query option

Quite quickly you can now add the options that you need. in your expression.

Filter Query in SharePoint Get Items in Power Automate Microsoft Power Automate Expressions with AddDays and utcNow 1

And before you know you have multiple filter queries configured with some boolean logic. It is even easy to make the queries a bit more complicated. As shown below I have 3 separate part in my logic.

Filter Query in SharePoint Get Items in Power Automate Microsoft Power Automate 3 options added 1

How many times have people contacted me with a query like this

(....) and ( ... ) and (...)

Well it will not work, why not? Well we will see this very soon.

Time to switch the experimental features off. Again make sure that you first save the flow! En when we then reload our flow from before we get given the filter query that we need:

Filter Query in SharePoint Get Items in Power Automate Microsoft Power Automate disable the Experiemental features again 1

The boolean logic has the right brackets. Did you know that and and or can only handle two parameters? Well this is why it failed so often in the past. Also That ugly internal fieldname in SharePoint, I didn’t have to type that all in. Or how about getting the startswith syntax right?

I wouldn’t use experimental features in a production environment, but temporarily using them might just about be very helpful. One word of warning please do create a backup of your flow first!

17 thoughts on “Filter Query in SharePoint Get Items in Power Automate

  1. Thanks for elaborating on this!
    It might come in handy (if I don’t forget about it when needed ;-))

    It is especially good to know about the (max) two parameters to AND and OR!
    I may have stumbled upon it in the past.

  2. Thanks for your post. My filter query with 2 params using AND used to work but no longer do so. I had to get rid of one param and then it worked fine again. Will try out the expt. feature. thanks.

  3. What I’ve been stymied by is a list where the item might not exist. I can filter on the primary key field, but when the record isn’t there (very possible on the business logic) the flow wants to stop. I can’t test length to see if I got a hit, it’s already aborted. I can build an OnError follow up to set the record found to no, but I’d have to bend over backwards to let the logic continue as if nothing wrong happened.

    Is there an equivalent to just test if the record exists before proceeding with get item or items?

    1. Yes I would check if an item exists first ( using a get items). If you use the run after settings you will find that there are failed actions, which I try to avoid where possible.

      1. What I settled on was a get items with a greater or equal than filter, a top count of 1, and a test both for the length of 1 vs null and of the returned first key of equal my query key or not. That avoided the loop through. Thank you for a very fine site full of excellent advice.

  4. How do you ‘finish’ entering an expression? There’s no ‘update’ button or anything like that. Sorry if I’m being a bit thick!

    1. I assume that you have the experimental features enabled as it isn’t as clear when you use those. You can type the expression and then close the expression editor either by closing that dialog or clicking on the action that opened the expression editor.

      1. I’ve tried clicking the X in the top right corner of the expression editor, and clicking back on the action, but neither seems to work, perhaps I’m missing something obvious? I did a quick recording here if you wouldn’t mind taking a look: https://photos.app.goo.gl/48RyqjoWTXN5WnJGA

      2. In the experimental version I actually do think that the expression was added. when you go to the little box after closing the editor and hit the up and down arrows you will find the expression. Not great! but still working-ish.

      3. UPDATE: after turning off experimental features, I found that the expression had in fact been entered multiple times! So it’s a browser UI issue rather than a PowerAutomate issue perhaps? I think I’ll stick with the non-experimental version for now… but thanks for your help!

    2. I find that at times I also don’t have the update button, which I ascribe to the UI getting confused. Saving the flow, closing that tab, and opening fresh again from the bookmark to edit my flow once more clears that up and the update button shows again.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.