When you want to filter an array in a Power Automate flow, there are a couple of challenges you might face.
Other array operations
Table of Contents
A while back wrote a post about arrays in Power Automate. One of the things that I didn’t look at was filtering arrays. Filtering arrays is actually really simple.
Power Automate has filter options available to make things easy. Before I filter an array I will first create an array.
Create an array
I started by creating an array. The array starts with [ and it ends with ] and each item inside the array starts with { and ends with }. All standard json stuff.
You could of course also use a compose action instead of this but it doesn’t matter too much.
You might also have an action that generates an array and you haven’t got any option to specify filters on the action and the action returns too much information.
Filter Array or Collection
Now I only want to select the items that have IsOk set to Yes. This can be done with the Filter array action.
The filter array’s condition can be set in the advanced mode to
@equals(item()?['IsOk'], 'Yes')
Or you could set the left hand side of the condition to item()?[‘IsOk’] using the expression editor and then set the right hand side to Yes.
The conditions here could include any kind of query that you would also use in conditions in your flow. So if you want to filter an array in you Power Automate flow, using the Filter action and set your condition.
Power Automate is sometimes just that simple!
[…] you want to filter your array data then please have a look at my filter data in arrays […]
I’m trying to pull multiple lines of data from an excel spreadsheet into a word template using MS Flow. Would an array work to pull the information.
Are you using MS graph or are you using the excel connectors?
Pieter,
I’m using Excel Connectors.
Hi Patty, I would probably recommend using the Graph api to read data. ( https://veenstra.me.uk/2018/10/17/microsoft-flow-read-large-excel-files-within-seconds-without-creating-tables-using-microsoft-graph/ ) It avoids all the troubles with locking Excel files. You can however use the Compose action as well and simply select the output using the dynamic content.
Can you please help me? I have a string in Logic Apps which is 40 chars long and I need to trucate it to 30. I don’t see a function like that and I have googled for hours. If you have any suggestions please let me know. Thanks! MIke Kiser
I think that you are looking for the substring function
Every day you make my day! Thank you very much for your help with Power Automate! It’s a great tool, but sometimes simple tasks become nightmares in Power Automate.
Hi, I’ve used your posts quite a few times, so thanks. My current problem is that I cannot get a Power Automate Filter Array action to work with a Contains operator applied to an array obtained by a Get Items action. The column I’m trying the filter on is a multivalued choice column. Please have you come across any problems doing this?
Regards, Stan.
Hi Stan,
Multi select choice fields or multi select people fields can indeed be complicated. You might have to push the records through a Select action and using this action reshape the data a bit to make the filtering easier.
To access a value from the filtered array, use:
outputs(‘Filter_array’)[‘body’][0]
(Zero (0) is for the first column, 1 is 2nd column, etc.)
Yes, that is possible and have looked at this in some other post
what if I want the last instead of the first?
There is a last function that can be used
I need to compare two arrays that are similar but not column by column the same (one has more columns than the other); two array elements would be equal if three columns have the same value. I know this works: Filter array A, B contains items() would give me all elements that are the same while not contains would give me items in A not in B.
Is there a way to create arrays from arrays just with a some columns? If I can do that, then I can reduce the arrays from Get rows/Get items to just what I want to compare.
Even better would be a way to Filter array using the three columns. I have tried
@and(equals(outputs(‘get_rows’)?[‘col1’],outputs(‘get_items’)?[‘col1’],
(outputs(‘get_rows’)?'[‘col2’],outputs(‘get_items’)?[‘col2’],
(outputs(‘get_rows’)?[‘col3’],outputs(‘get_items’)?[‘col3’]))
But that gives me an error when I try to save the step.
Questions, then:
1. Can Filter array allow selection on multiple columns, and if so, what is wrong with my expression?
2. Can I create arrays from the arrays resulting from the Get rows (SQL) and Get items (SharePoint) arrays that contains just some columns so I can compare elements in Filter Array?
My strong preference is solving #1.
By the way, I have made a number of attempts tackling this with Apply to each, but that swells and swells with nested Apply to each as I create conditions.
Thanks in advance.
I made some headway. After each array is created (Get…), I added a Select action specifying the three fields that will be used in a match. Then a Filter array action on the two outputs (contains or does not contain) gives me matching or not matching elements.
Voila. Except I need all of the columns in each original array which disappear after Select, so I need to figure out how to reverse direction; go from the array from Filter array on the Select outputs back to the original array elements.
One of the failings of Power Apps reaching its full potential to be codeless and for the average user is it’s byzantine reliance on JSON expressions and convoluted action definitions. While I don’t object, I have much less technical users who throw up their hands and roll their eyes. Worse is when I try and explain a level of complexity or difficult to a business owner.
Thanks for your help
I see the same problem with Power Automate. People are able to develop the sime stuff themselves. When it comes to real business solutions they often need help.
This is where often consultancies can help to get past the hurdles.
I have also tried with the many patterns on my site like try-catch, Pieter’s method and querying json to help.
Maybe software development is just a speciality. There must be a reason why some people study for many years to become a software engineer.
Hi,
I want to filter with condition [‘M#C’] = “1” from below array. I tried with Filter Array action but fails due to # cahr in keyname. Please suggest me.
[
{
“M#C”: “1”,
“name”: “abc”
},
{
“M#C”: “1”,
“name”: “avy”
},
{
“M#C”: “2”,
“name”: “avy”
}
]
Convert it to text and replace the # with something else. Then convert it back to json.
The only part that is not intuitive is having to go into advanced editor mode and do the item()?[‘yourKeyHere’] syntax. Usually you can get away with the simple editor but occasionally you are forced to go advanced. I am comfortable doing that, just wish it was clearer *when* you need to go that route. Maybe it’s a boolean thing. I dunno!
I totally agree. Once you have mastered the editing of the expressions all becomes easy. But that hurdle is for many people just a painful journey.
Hi Larry,
I’ve been running into similar challenges… wondering if you had any luck getting further with this?
Thanks,
Colin
Hi Colin,
I have fond recently that the apply to each steps ( as long as you don’t use variables) can be quite fast. Whenever you find that you need nested apply to each steps use Child flows instead. When kicking off the child flows in parallel they are started at high speed. This reduces the run time dramatically, making it easier to get these slow processes completed within shorter periods of time.
Thanks Pieter,
That’s good to know – I may give that a try with one of these flows.
It does still seem that there should be a way to do a lookup from one array to another though, without doing an apply to each…. C