When processing large amounts of records in PowerAutomate you can quickly loose patience. The loops tend to execute quite slowly and that might not fit every case. Here’s how I sped up my flow.
Here’s what’s hapened
I had to process about 3k records in PowerAutomate. The requirements were simple – for each record, I had to check if there are gaps in the serial number sequence. My client had the serial numbers in the following format: DB123456. I needed to check if there’s any record missing in between.
I thought that this should be simple – and it was! I sorted the records by the serial number, prepared the variable holidng the first serial and then ran through the records using a loop, parseing the serials and checking the difference along the way – easy!
Well, it turned out that to check each of the 3k records, it would take the procedure about an hour and a half to finish – each iteration took around 2 seconds.
I was bummed. That was way too long. I had to make it faster.
Feeding the slug with steroids
My first idea was to enable concurrency – In PowerAutomate, you can get the engine to process up to 50 records at once just by toggling a single switch.
But I had an issue – I had a global variable holding the previous serial number. Running this loop in parallel wouldn’t quite work – the items would no longer get processed in sequence and that would mess up my checks. So I had to find an alternative. To get rid of the variable, I’d need to get the previous value directly in the loop. I’d need to access the current iteration index, just like in any other programming language.
But it turns out, getting a current loop index isn’t quite possible in PowerAutomate. Other solutions online suggested using variables… I had to find another way.
Getting the indices anyway
Then it struck me – instead of iterating the list of records, what if I prepared an array of indices, just like in Python…? I looked through the expression functions and there it was – the range() function.
It receives the starting index and the number of elements you want to create. I passed 1 as the starting index as I wanted to start from the second element and used the list length as the second parameter.
It worked! Using the items() function gave me the current index. Now I was able to access both the current and the previous record – WITHOUT VARIABLES!! Perfect! ✨
And it worked with concurrency as well. This reduced the total time to around 3 minutes! ⚡⌛ This was already good enough and I could stop. But I went just one step further.
Using the Select block
I replaced the loop with the Select block. I used the range() trick again to access the current index. Using the Select block, I was able to prepare an array of objects containing a difference between every pair of records. The last step was to use the Filter block to get only these records that had difference greater than 1 and I was done.
Replacing the loop reduced the total time to 3 seconds. ⚡⌛
Conclusion
When processing large amounts of records, loops are not always an answer. While you cannot always easily replace them, using single block operations instead of loops will definitely help you speed up the processing.
