Yesterday, I noticed John Liu and Brian Dang discuss Pieter’s method. This method is referring tot my post of using a compose action inside an apply to each and then referring to this Compose action outside the Apply to each.
If you understand Euler’s Method and L’Hopital’s Rule then I’m sure you can understand Pieter’s method mentioned in Brian’s tweet!
But it is not enough! It is time for the advanced Pieter’s method.
The advanced Pieter’s method
Table of Contents
How about other actions inside an Apply to each?
Today I created a flow which gets items from a SharePoint list. The SharePoint list has a multi-people field and I want to just create an array of all the email addresses of all the people that are selected in this People field.
I’m using an Apply to each to run a select and then I’m collecting the output from all of the select outputs in a single Compose action.
As you can see in the above screenshot I’m getting an array of arrays.
Generate the array of arrays
In my Apply to each I’m taking the results from the Get Items action and let the Apply to each action process these items.
Then the select takes the Apply to each items and just selected the People field using the following expression.
items('Apply_to_each')?['People']then the mapping to the Email field is easy by using:
item()['Email']
This will generate the array of arrays. All that is now left is to get the result output the Apply to each using the Compose action with the following expression:
Body('Select')And when we run the flow we are getting the array of arrays as shown earlier in this post.
Performance
You might have noticed the slow performance of the Apply to each action. 26 seconds is not acceptable!
We need to apply concurrency settings on the apply to each.
After enabling the concurrency setting mentioned in the above post. the apply to each only takes 2 seconds to process 100 items.
In my case the items in the compose were still listed in order of my data feeding the apply to each. I’m however not 100% sure if this is something that we can guarantee or if I was just lucky. In my case I didn’t worry about the order of the data anyway.
What’s next
When looking at performance and you need to build up an array of data you could consider using this method. So far I have looked at the Compose and Select but is there any reason why other actions couldn’t result into the same?
Could we for example use a Get Item action from the SharePoint connector to build up a list of items?
I’m going to put that to the test!
Using my same flow as before I will now include the Get Item action to get each of my 100 items from before.
Then I will collect the body of the Get Item action after the apply to each step.
I got my array of list items back. Ok, I can imagine that this isn’t too exciting as I could just have used the get items action instead of the get item. But now imagine …
You have a list with car brands. Then for each brand of car you have separate lists with models. The get item action inside the apply to each could look at different lists depending on the the car brand processed.
This could create a lot of options. If you have taken advantage of this method then please let me know. I’m interested in hearing your use cases.
Hi Pieter,
Thanks for sharing wonderful post , I have question regarding filtering array using another array in fastest way. Could you shed some light on it.
Thanks
Hi Manish,
Do you mean with filtering an array using another array that you would want to remove items from Array 1 if they exist in Array 2?
You could use a condition inside your compose using an if function in the compose action.
Hi Pieter ,
Could you share some more details on it. I have used apply to each and filter array inside loop to filter the first array based on second one.
Thanks
Ok, so you have two arrays
array 1 : [1,3,5,6,7,9]
array 2 : [1,5,8]
Now we only want to get the items in arrays 1 that exist in array 2 resulting in [1,5]
Is that what you are trying to solve?
You could probably just use the Intersection option for this:
https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#intersection
Alternatively if the data is slightly more complicated then you could use the method described in this post and in the compose use the if function like this:
if(,, null)
You will now end up with an array of items and blanks, which can be filtered out after you have run the apply to each with a filter action.
OK here is what I am trying to achieve :
Array 1 : [{“id”:1,”data”:2,”code”:3},{“id”:2,”data”:3,”code”:4},{“id”:3,”data”:4,”code”:5}]
Array 2: [{“data”:1,”thread”:1},{“data”:2,”thread”:2},{“data”:3,”thread”:3},{“data”:4,”thread”:4}]
Output : [{“id”:1,”data”:2,”code”:3,”thread”:2},{“id”:2,”data”:3,”code”:4,”thread”:3},{“id”:3,”data”:4,”code”:5,”thread”:4}]
Thanks
I’ve solved it. I will write a post about this tomorrow.
https://sharepains.com/2020/03/20/create-joins-in-power-automate/
[…] el segundo veremos cómo usar una combinación de acciones simples y el método de Pieter, el cual nos tomará menos tiempo para […]
Wonderful stuff, thanks to this method my flow went from 3 min to 20 seconds.
Thank you!
That is great the hear. Can you gain any further speed on that? What does your flow do?
Hi, not to be negative but your solution to compose outside the Apply to Each with the body of the select simply gives an error in my flow saying I cannot use the values from inside the apply to each when I’m outside of it.
How many levels of nested Apply to each steps have you got?
This only works on unnested apply to each steps. So avoid the nesting and then it will work.
I think I can use this along with some follow on Select & Compose actions with join & split expressions to just combine the arrays. It could make the edit interface look a lot simpler than having many parallels branches.
But I’m also thinking we may be able to kind of hack Power Automate this way to go beyond the intended 50 parallels actions limit as each of those 50 branches could have an Apply to each running it’s own 50 concurrent actions… hmm
Hi Pieter,
very interesting approach.
I am trying to apply it to a common scenario, where an API returns paginated results like this:
{
“page”:1,
“total_pages”:4,
“data”:[array_of_records_page_1]
}
My pattern is like this:
– grab page 1
– build a range for the remainder of the pages and grab the next pages in a for each loop.
If I use the compose action outside the for each I end up with this:
[
{“page”:2, “total_pages”:4,”data”:[array_of_records_page_2]}
,{“page”:3, “total_pages”:4,”data”:[array_of_records_page_3]}
,{“page”:4, “total_pages”:4,”data”:[array_of_records_page_4]}
]
So an array of arrays.
I would want to be able to, as efficiently as possible, union all page data into a single array:
[
records_page_1,
records_page_2,
records_page_3,
records_page_4
]
The way I currently do it is by using a combination of a variable and compose actions to do a union in my for each, but I am hoping for something smarter.
Thanks
Convert each of the 4 arrays to text. Then take the first and last characters away. So that is the [ and the ]
Then concatenate the texts and place the result inside [ and ] and then convert it back to json.
I discovered a better method, which works even within nested Apply to each.
Using the Select connector,
Select From the output of Get Items (navigating down the json hierarchy like outputs(‘Get_items’)[‘body’][‘value’][0][‘Users’] if necessary), then
Switch Map to text mode and enter just item()?[‘Email’] (i.e. no mapping).
The result is a simple array of email addresses in the body of the output, which you can refer to with body(‘Select’).
https://i.postimg.cc/dtpdLYYg/Select-method.png
Hi Nick,
Using select action can indeed help with the performance. however they only work for situation where you only transform data inside the ‘apply to each’. If Your apply to each needs more complexity than just compose/data shaping then the method described in the post is needed.
I think that the performance imporovement that you are describing is like this:
https://sharepains.com/2020/02/10/1-flow-performance-tip-to-get-your-flows-out-of-the-stone-age/