How To Improve Performance In Power Automate By Using This 1 Super Charged Action

Jon Manderville
March 27, 2024

Until time actually costs you $$$'s or causes frustration, you can be satisfied with not caring how long a Power Automate process takes.

However, you might be looking to replace manual steps using Power Automate flows and the last thing you want is for the result to either to cost you more money than it should or take longer than a manual process might.

Power Automate offers many time saving capabilities, but also because it's developed on a low to no code principles, may offer up a solutions to a problem that are actually less efficient (financially or in time) than doing the step manually. 

Take this example:

  • You need to create a Document Library in SharePoint for every new client who's emails arrive in your inbox.
  • Your clients send in their name details on that email - sometimes with some random characters or formats that you won't be able to use in a folder name (like a * or an exclamation mark). 

Searching data and replacing at volume is a Power Automate strength. But consider the timings from 2 Power Automate flows below which perform exactly this exact function.

apply to each power automate flow run
select power automate flow run

Notice the Average Run duration statistic. On the left it says 12 seconds. On the right it says 257 milliseconds

Both flows accept an input (I have manually entered it to simulate a client name as it might be written (using "Acme Client number [1]! (special message email) this is important**" as my piece of text) and search for characters that would not work if I were to create a folder with their name automatically.  

One is clearly more efficient both in time and - if you were to be using a Pay-As-You Go licence in Power Automate - processing $ than the other. 

As context, if I were to perform an exercise to do this manually, I could visually scan an email pro-forma that pops into Outlook from a client and create a new folder in roughly 10-15 seconds with the same name - that aligns to their entry (but also works for SharePoint Document libraries).

I would probably lean on copy paste and maybe just delete the characters I do not want before pressing create for the folder.

It may take a little more than 15 seconds as I open up locations but roughly thereabouts feels fair. 

Now, the flows above don't perform all the steps I have described so they represent slight underestimations but hopefully you can see that in one case, you may not be getting huge efficiencies using Power Automate but in the other case, the process is ALMOST instantaneous. 

How Does Power Automate Typically Work With Loops?

First of all, here is the entire flow for the example on the left. Don't worry that you cannot see the details. I have summarised on the right hand side but will happily share if you have a need. Just get a sense for complexity and know that this flow is not quick to run even with this one simple Apply to each loop. 

Example Apply To Each Flow

Flow Process

  • Store a string with non-desirable characters
  • Store data from your email form
  • Turn the strings into Arrays (you need to do this to iterate through them)
  • Set up an Index to use for cycling through the string you got from email
  • Set up an Apply to Each loop. Cycle through EACH character in your string and compare it against the non desirable string characters
  • In the Condition, test the string character (now in an Array) to see if one of the non valid characters is found. 
  • When you find a non-valid character match, place the string character in the left hand branch. For 'valid' characters, place them in the right branch. 

Using this approach, you could continue the right hand to reconstruct a string with ONLY these valid characters. I have not as the flow would get too long for the needs of this example but you get the idea I hope.

This flow remember takes 12 seconds on average to run. With a string of 40-60 characters in length. That is not great.  

Why Is Apply To Each Slow?

Power Automate is a visual representation of underlying code that gets executed. Each action you see above represents both computational power to show it and then execution of the code under the hood.

So if you think about it, there is both execution time and potentially cost for each action you create. It follows that using less actions is a good thing if you want to cut down time for your flows.

Also think about what the Apply To Each is really doing. It is setting up a test for EVERY item in the string you gave it (in my test example, there are 58 characters to check). 

Now imagine that each cycle performs ALL the actions within the loop. It runs the condition, makes a choice, then runs the actions in the relevant leg. 

Although you will not be able to see them, when you run an Apply To Each loop in this example, the same actions will execute 58 times in a sequence in this Power Automate flow. Apply what you know about an action meaning execution time and cost and you can begin to comprehend why this example - and using Apply To Each - is quite inefficient. 

Now imagine repeating this for several emails in a batch. Timings and costs may not look pretty. 

A condition in Power Automate

To give you a head start on validating this issue, if you wish to create a flow like this (maybe to prove the concept) here is the expression that is used in the Condition. 

contains(variables('varNonValid'), substring(variables('varsSourceString'), variables('varIndex'), 1))

Worth noting; This may not be the best way to perform the test but it works and demonstrates the point. The test expression is used on the left hand side of the Condition and is checking if the outcome is equal to true.

Not a typical way to perform a Condition check but it works and allows me to cycle through each character in the string whilst also cycling each character in the NonValid string. 

If you want a live demo or to discuss how this is working in more detail, I'm always available in the Academy to help. Just send me a direct message. 

Join 11,000+ in the Collab365 Academy

Master Microsoft 365, Power Apps, Power Automate, Power BI, SharePoint with Exclusive Access to 450+ Hours of Expert Training and a Wealth of Resources!

Want A More Efficient Way To Check For Special Characters?

As you know, there is another way. This method is faster and requires less actions. For reasons already stated, less actions is good. Not only that, but it is even by a conservative estimate 20X Faster most of the time. That doesn't mean 20x cheaper but it will most certainly be a lot cheaper if you are running these processes at scale. 

SELECT Example

Flow Process

  • As with the first example, store Non-Valid Characters and a string for use later
  • Use A Select Action to create an array. This is the golden piece of the puzzle to dig into. 
  • In the From Parameter of your Select, set out the array using this expression:-
Select From parameter
  • Build the Array Map using the following:
    • Index - indexOf(outputs('Non_Valid_Characters'), substring(outputs('Text_String_To_Validate'),item(),1))
    • The purpose of this formula is to check if any character within the Text_String_To_Validate output is contained within the Non_Valid_Characters output. it is used to validate each character of the string by checking it against the set of non-valid characters. The indexOf expression returns a value of the position if a match is found. If it is other than -1, it means the current character is a non-valid character according to the list provided by Non_Valid_Characters. You will use this later in the flow
    • Char - substring(outputs('Text_String_To_Validate'),item(),1)
    • This will store the character you are looking at into the array alongside the index value you created above for each letter you are checking. 
  • Next in the Flow you will use a Filter Array action and only keep items where there is a value of -1 in the Index field. This means it is not an invalid character. 
Filter Array
  • Finally, again using a Select Action with a slightly different parameter setup (found using the little icon on he right of the map box, you enter these Dynamic Values). Select the Cha field from the Array this time only. 
Choose only Chars
  • The Final Step  can seen in the example is a compose which uses a Join expression to put each 'cleansed' item from the array back into a string. 

You can now generate a file name or use or to ingest into another action should you need. You'll know that characters you do not want have been removed. 

Why Is Select So Much Quicker? 

Knowing how to create the same process in different ways is one thing. Knowing why Select is so much more efficient is another. 

Here is an analogy for what is happening to make Select over 20x quicker in this case.

  • Think of a fruit basket with assorted fruit in.
  • Using the hand that is not holding the basket, remove each item in turn to decide if it is one of 10 fruit you really like.
  • Both hands are now engaged in activity. One holding the basket, the other holding a piece of fruit. Your eyes and brain are also assessing EACH item. 
  • You have to remove it and look at it before deciding what to do with it. 
  • In each case, you inspect it, cycle through the 10 fruit you like in your head and then make a decision what to do with it.

That is what Apply to each is doing for you. Now here's what Select does

  • You decide up front what 10 fruit you like. 
  • Select is given this list and all at once, it scans the basket assessing all items for you (using the baskets innate ability to process all items in parallel)
  • It then marks only those it knows you like for you so you can press the trapdoor in the basket to only leave items you like in there. 

Next you continue shopping. You didn't have to use 2 hands to perform the check. You just got the basket to do it for you whilst you continued along your way picking up shopping with your other - now free - hand. 

The reality is that Select is exploiting the parallel processing power of the codebase that sits behind Power Automate in a much more effective manner. Making the actions needed both lower  in volume and more efficient at the same time. My analogy may not work for you but I hope it gives an idea as to what is happening.  

Should You Use Select For All Looping Scenarios?

As ever, the right actions make sense only in the right context. If you are searching through and choosing lots of data based upon some parameters you can define, give Select a go. Apply to each is always safe to use and the go to for a lot of scenarios but keep an eye on that execution time and know you have another option if it gets too long. 

We'd love to hear how you use Select over at the Academy so pop in over and let us know! I hope this post has been useful but if you are stuck or want to dig into these 2 flows with me. I have kept them safe and would love to share and learn from your experiences using Select as well. 

See you on the Academy! 

Join 11,000+ in the Collab365 Academy

Master Microsoft 365, Power Apps, Power Automate, Power BI, SharePoint with Exclusive Access to 450+ Hours of Expert Training and a Wealth of Resources!