Power Automate Join or Merge Arrays Efficiently | No Apply to Each
Vložit
- čas přidán 12. 07. 2024
- Learn how to merge or join multiple arrays super quick with no apply to each. Using string techniques we can rebuild an array to form an object and access data directly using a primary key value. I will demonstrate the speed of the solution on three arrays containing 5,000 records, joining them one to many and one to one to form a new array in under 10 seconds.
This works in Power Automate or Logic Apps and on all data sources, SharePoint, Dataverse, APIs, JSON. Join related data in seconds, not hours.
00:00 Intro
00:52 Three datasets in SharePoint
01:52 Getting started with Power Automate
03:09 Understanding Array, Objects and Expressions
05:42 Repurposing an Array to form an Object
10:50 Joining 3 Arrays to form a new Array
16:19 Flow demo joining Arrays quickly
18:03 Outro
JSON Videos:
5 Challenges: • How to write expressio...
Basics: • JSON Array in Power Au...
Native JSON Beautifier for your Edge Browser learn.microsoft.com/en-us/mic...
#PowerAutomate #JSON #Array
Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕ - Věda a technologie
I can't express how helpful it is in words!
Tell me what it’s helped you achieve 👍
Great, great vid Damien! With a bonus tip on JSON beautifier! Awesome! Thank you so much!
Excellent. 👍 I did not know this lookup was possible. This will be very useful at times.
This is awesome!!! Thank you so much!!!
Really Brilliant! You are forcing me again to recreate what you did to materialize the knowledge. You're a great teacher. Thank-you.
Cheers Stephan, Thanks for your kind words.
Just want to add to the chorus by thanking you for an excellent video. Had a couple of hiccups - with semi-colons rather than colons - but that was user error. Absolutely first class - thanks again!
Thanks Paul.
The timing of this video couldn't be more perfect!!! Trying to figure out how to pull data from 2 fields base a common location code and then perform certain actions on it. I think understanding this technique will be ideal for my use case. Thanks for sharing.
In my mind, this video compliments czcams.com/video/PD980sKKx0E/video.html which demonstrates how to use an external data source to simplify branching. Glad it's been helpful Douglas 👍
This is great, thank you!
Brilliant!! ❤
Next level stuff!!
this is so devilishly clever, I am beyond impressed. Helped me greatly, thank you.
Tell me how much faster your flow runs 😱👍
I was creating a flow to sync some data between devops and sp list, initially tried loops but it was painfully slow. With your method the whole thing takes few seconds.
@@kfasekk amazing 👍 thanks for coming back and sharing. Really happy to hear it’s helped.
this helped me immensely thank you!
Nice one, please tell me more. Was it efficiency you achieved or a better understanding of select?
I was looking for the equivalency of sql join statement for API calls to start building a power app for our field crews. The API returned one data set with an id field that needed to be mapped to another api call that had the english translation for the id field.
@@DamoBird365
Thanks master ♥
This is fab! Thank you so much!
Thanks Karolina 👍
Thx for mentioning coalesce RE null values and json beautifier. GD
Coalesce is definitely a handy one to know about for that reason. Saves a complex nested if.
Clever idea 💡 Nice one ❤
Similar approach can be achieved using xpath inside Select expression - which one would be faster for large lists (what do you think @Damien / @Paul) ?
@@piotrrusak I haven’t tested but I think they would be similar - there is a video on my channel on the xpath method. They would both only consume a single action, and the select action always seems to do it’s work fast.
You’ll need to test it for your scenario. I have had performance issues with xpath. I had considered featuring side by side in the video as larger data sets get slower exponentially in xpath. But like any solution, test and go with what suits your scenario. It’s worth knowing about both options. When I tried to map multiple fields using xpath it took 10s of minutes. This method was over and done with in 10s of seconds. But maybe Power Automate was having an off day.
Really Nice Learning.! I had to go through it twice and build the solution. I only used 10 orders and made the mistake of using the SharePoint Title column (which always gets in the way) for CustomerID--thus it was Title. Didn't know you could use objects like this. Also, didn't know the Select could draw from different arrays at the same time.
Great!!!👏👏
Thank you! Cheers!
super help.
Cheers Philip
👍 I always feel so lucky when you type out those lengthy concat expressions and just click update without copying it first into your clipboard. #norisknofun
Living on the edge there 😂 I’ve also seen power automate tell me my expression is wrong and I click update a 2nd time and accepts. Come on Power Automate, I know how to write an expression. I am sure I dropped repurpose in there today too.
Very Helpful. Any hints on how to save this join to excel and filter?
You could watch this ? Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate
czcams.com/video/gtlklzi6MDg/video.html
Love the video--still working through. Had to setup some lists. One point of confusion--you say semicolon when I think you mean colon. (Just trying to get the syntax right)
Possibly, the physical expression onscreen should be correct? My poor brain during all of this. Sometimes hard to coordinate my mouth and my hands at the same time.
Thanks, great method. How do you apply it for more than 5000 records for a very Sharepoint list?
I’m not sure what you mean? You can use paging to get more than 5000 items and the logic is identical.
Thank you so much for the great information. Can I use the same method for Dataverse? Example: I want to collect all activities of related to the account and sort the last 5 activities based on the created on field.
I believe it will be possible. This is 1-M? I need to try it but theoretically, yes. Let me know how you get on.
I followed your steps but using two arrays instead of Get Items. My key field is called 'jobNumber' as that's the only field between the 2 arrays that match. So in the final Select action I'm either referencing item()?['fieldName'] or the long outputs formula you provided. All works except it's only returning results for a single 'jobNumber' when I have many 'jobNumber's in my arrays. Is my need excluded from this method, leaving me with looping?
Are you saying you have, for example 10 job numbers but the final output is 1? You must have a mistake in your build. The job number in input should match the number of objects in output.
Where does the new array is stored? How can we create a new SP List by combining these 2 lists?
The array is still in the output of the select. If you want to write it to a list, stick the output of the select into an apply to each.
But what if for example a customer didn't place an order. His id will not be found in the orders tabel. When I try this method, i get an error. How can i handle this?
What’s the error? Do you use a ? In your expressions? It should return null.
Hi Damo,
I have the same procedure and i am getting error for compose action.
Unable to process template language expressions in action compose inputs at line 0 and column 0.
I used the same expression as you mentioned and the same is working with json() but when i am trying to use json() i am getting the above exception.
Can you please help me with this
You can fix it by choosing a column with unique values in the select statement. :-)
Assuming two identical Array’s, how would you merge records based on their position in the array. i.e. item()?[0] from one into item()?[0] of the other?
In my Flow, I’m stuck on this part `outputs(‘Compose_Customer_Object’]?[item()?[‘CustomerID’]]?[‘FirstName’]`.
What is I don’t want to do `item()?[‘CustomerID’]` to merge based on ‘CustomerID’?
What if I simply want to do `item()?[0]`, where [0] is the index for each line?
I can’t do any Apply to Each actions. My array’s are like 200k long lol.
Use range(0,length(your array)) as input to a select action and you can get each object where item() is the integer.
@@DamoBird365 amazing. worked a treat. thanks 👍
Flipping awesome 👍
Good, clear explanation Damien!
I remember doing this type of join in the SharePoint Batch Update template, but deciding it would be too much to go through how it works.
czcams.com/video/l0NuYtXdcrQ/video.html
I’m glad I can point people to this piece now.
Great minds think alike. I've used it a couple times before, like for Planner GUIDS. I notice you have done it slightly different but the same idea of Id:Object. Good to see another example out there 👍 shaving some time off of our flows.
Hopefully I know what's coming. 😉
I didn't! Nice!
@@ukm365 what were you expecting?
@@DamoBird365 Oh, I was absolutely expecting the the selects, but you went over and above here. 🙂
Brilliant stuff!
Ahhh, I do love a select 😉🥳
@@DamoBird365 innit ... and with an xpath() inside? You're gonna have to give me some alone time! XD
Hi Damien. I'm getting the following error when I attempt to create the Object in a compose action. Any ideas where I went wrong?
"InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'Json' parameter is not valid."
I am honestly not sure, you can share the expression or alternatively try the forum.
Never mind. I figured out that I put in a semi colon instead of a colon in the select step. Duh! Brilliant work Damien!
Very nice! But You should think to speak more slowly. People from other countries are watching you.