Before we start, quick call out. Although a fundamental of programming, loops are also a nightmare. They are processer (aka in Power Automate API) heavy and often used very wastefully. We should always minimalize our loops by:
- Use them Less
- Minimialise their length
- Less actions inside them
- Avoid loops within loops (and definitily loops within loops within loops)
That said in most flows we will need to use a loop, so I wanted to look at:
- Different types (and when to use them)
- How to be efficent with them
Different types
There are 3 ways to loop in Power Automate (well more but these are the out of the box standard ways).
Appy to Each
The one we all know and the one Power Automate defaults too. If you select and item in an array it will be auto pushed into a Appy to Each loop.
Appy to Each are great for (you will never have guessed this) any array you need to touch every item. The loop will action every item in the array, and if the array is empty skip over and continue.
As the loop handles the item the syntax is really nice, as you just reference the field in the item like you would if it wasn't in an array.
Do Until
Do Until has the same abilities as the Appy to Each with additional functionlity (but loses the nice syntax).
I've gone into more detail about them here Why you should love do untills, but their main advantages are:
- They don't need to complete, if you are looking for something you can stop as soon as find it.
- They can be relative, as they use an index to find the item you can also reference the previous/next etc.
- In the same vain you can have bigger steps (Appy to Each do every item but what if you wanted to do every other).
- Limited range, like stopping when you find something, but you are also able to set limits like top 100 or for first hour.
Office Scripts
Office Scripts are the wild card in my list, not technically part of the core actions (as it works outside of the flow) but they are standard and everyone uses Excel.
Because Office Scripts leverage TypeScript/JavaScript they can also do loops. They have the ability to do Appy to Each (forEach), Do Untill (doWhile) and additionally for.
Being in code they are exponentially faster and only use one Power Platform API call, but there are a couple of big negatives
- They can only manipulate data (cant call actions like send email)
- Are limited by size (2mb) and run time (2mins)
- Require addional coding skills for developer
- As out of Power Automate add complextity to ALM process
But if you are manipulating data, or working with Excel then Office Scripts are the best loop option you have.
How to be efficent with them
Pick Right loop
This is the most basic, don't just use the Apply to Each action. It maybe the right one 90% of the time but not 100%. If you are looking for something then use a Do Until, if you want to loop over Excel rows use an Office Script.
And never use it as a look up for one item, use first or an array postion.
First
first(triggerOutputs()?['body/attachments'])?['id']
Array Position
triggerOutputs()?['body/attachments'][0]?['id']
Personally I'm a fan of Array Position as can be used for any index
Filter/Query
Another easy one but we should always shorten the loop by removing unnecessary items. The Query is the best if you can (though things like return null are not possible in the SharePoint query), and remember the Filter is the same amount of API calls as a Get Items, so don't do a Get Items and 2 filters, just do 2 Get Items.
Move out of Loop
Another simple one that just requires some planning, every action in the loop should have the item as an input/chain input, if it doesn't then it shouldn't be there. This could be simple things like a Compose or more complex like a Condition.
While on the subject, conditions should never be in a loop, you should be using the stack technique.
The Stack technique is when you filter you data and process in a stack.
There are 2 main reasons.
It's a lot easier to read, if you have an exception you can see exactly what condition caused it.
It's a lot more efficient, check these numbers out
You have 100 items, 50 true 50 false, with the condition that is 201 API calls
Get Items 1
Condition 100
Item Action 100
Now if you do Get items query true, the query Ger items false you only use 102
Get Items 2
Item Action 100 (50*2)
Stringify/index of
This one is cool, we don't have to treat our arrays as arrays, we can turn them into a string and manipulate.
A good example is looking for a value, you could loop over checking each value or filter and count rows. But a better way is to turn into it a string and do an indexof to check to see if it is found.
Another trick is an array of arrays you want to convert to one array. Instead of a loop within a loop approach you can convert to string, replace, and then convert back.
Let's say you have below array
{"body":[{"ID":1}]},{"body":[{"ID":2}]},{"body":[{"ID":3}]},{"body":[{"ID":4}]},{"body":[{"ID":5}]}
and you want:
[{"ID":1},{"ID":2},{"ID":1},{"ID":2},{"ID":3},{"ID":4},{"ID":5}]
Then this expression will stringfy it turning it into a string, trimm the start and end, and replacing the keys.
replace(replace(substring(string(Outputs('Select')),9,sub(length(string(Outputs('Select'))),11)),']},{"body":[',','),',,',',')
Hidden Arrays
What is not commonly known is any action within a loop is added to an array, that array can then be referenced outside of the loop.
Not make sense, let me explain. In the below example Im looping over an array and looking up an email address, insead of storing that return in a variable array I can reference the Get_Items from outside the loop. Power Automate hides the array so you can select it, but all you have to add is use the Outputs('') expression.
As its an array it can be referenced by any array action (Compose/Select/Filter/HTML table/CSV table), you just have to remember it returns the entire response, so you need to navigate to the right object to get your value.
Top comments (0)