If you want to traverse organisational hierarchies, walk through nested folder structures or create nested Power BI metrics from an Excel table for example, you need some sort of recursive logic. In this article I will introduce you to the basic method of recursive flows in Power Automate. In upcoming articles I will share some of the flows for the above mentioned use cases.
What is recursion?
Daniil Maslyuk contributed a great introduction into recursion on my blog here. Please check it out if the concept of recursion is new to you.
Here, I will take his basic example as a starting point to convert into a flow in Power Automate. The task is to multiply a number by 2 and then continue to multiply the result of the previous multiplication with 2 again until the result is greater than 10. In Power Query, you can solve this with the native recursion operator “@” like so:
In row 1, you feed in a number as an initial value for the calculation. Row 2 has the condition when the recursive repetition shall stop, row 3 defines the output in case the recursion has stopped (or the initial values is already greater than 10) and row 4 describes the operation that shall happen during recursion. “MyFunction” references the result of the previous iterative step.
Recursion in Power Automate
Conceptually, we can solve this in PA like so:
So we are going to use a variable that allows us to push the values from our actions to. That variable can then be queried and checked if the condition for further calculations is met or if the final result should be returned.
Technically, this can be designed like below. The process step numbers match in both pictures.
Array type variables for advanced calculations
Next, let’s try to do the “Hello World” of recursion: Creating a flow that calculates a Fibonacci series. This is a sequence of numbers where each number is the sum of the two preceding values, starting from 0 and 1.
So we need to create a series where the next value will be calculated by adding the last 2 values from the series so far. As we need the 2 last results, for each new calculation, our variable cannot hold a single scalar like in the first example. Instead, we have to use a variable of type array and append each result to it. Then, we have to repeat that calculation n times (where n is the number we are feeding in). Therefore we could use the “do-until”-action with an additional counter parameter or we can use a “apply to each”-loop if we create an array which is n elements long. Let’s go for the later:
Summary: Recursive flows in Power Automate
These are the 2 basic architectures on how to do recursive flows in Power Automate:
- Use one (or more) variables to store the results of the calculations so far and make them available for the next calculation steps.
- Decide which loop you need (“Do-until” or “For-each”) and pick the matching actions.
- If your variable is a scalar (single value), then you replace the variable in the recursive step (“Set variable” action) and if it is an array, you use the “append to array”-action.
Enjoy and stay queryious 😉