Power Automate Join or Merge Arrays Efficiently | No Apply to Each

[@unolaugh]

Beginner citizen developer here. This is the most powerful thing I have learned in the Power Platform in the two months I’ve been playing with it. Thank you!

[@DamoBird365]

@@unolaugh thank you 🙏

[@unolaugh]

@@DamoBird365 our initial primary use case loads daily static exports from external databases into an internal SPO to provide pseudo real time dashboards across multiple data sources. We are using your Select/Filter/Join video to find Left-Anti and Right-anti Joins between the source table and our internal table. However, I couldn’t figure out how to identify and report deltas between the sources in key value pairs. With this Select/Compose/Select Object strategy I can build the following object: { “OldKey”: “oldValue” “newKey”: “newValue” “deltaKey”: if(equals(oldKey,newKey), “”, “oldKey > newKey” } This allows for much simpler Filter Array actions to perform all types of Joins and Create HTML tables for updating our SPO Lists and reporting changes.

[@vozdesilencio]

Thank you, you saved my life. Power Automate is so limiting and frustrating but you found a clever solution.

[@DamoBird365]

Thanks Isa.

[@vozdesilencio]

@@DamoBird365 Using this option I had a problem with the object limit capacity, so I couldn't solve my problem for long data sets. Now i am trying it with your xml alternative, yours videos are very interesting

[@vozdesilencio]

with xpath i had same problem "The template language function 'xpath' failed to parse the provided XML" data overload. So no way to manage with large datasets.aunque como tablet sí lo tolera, debería haber función de filtro so solve this, or merge tables. Thank you anyway, your videos are very intelligent and usefull

[@cq401147]

I can't express how helpful it is in words!

[@DamoBird365]

Tell me what it’s helped you achieve 👍

[@scottryan5150]

I can't give this video enough thumbs up. I originally found this video, but tried to find easier solutions. Months later, and after much frustration, I came back to this solution and it works like a charm! Wish I'd tried this one first!

[@StephanOnisick]

Really Brilliant! You are forcing me again to recreate what you did to materialize the knowledge. You're a great teacher. Thank-you.

[@DamoBird365]

Cheers Stephan, Thanks for your kind words.

[@paulwagstaff8349]

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!

[@DamoBird365]

Thanks Paul.

[@emmanuelmaceda2475]

Great, great vid Damien! With a bonus tip on JSON beautifier! Awesome! Thank you so much!

[@dougydoe]

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.

[@DamoBird365]

In my mind, this video compliments kzbin.info/www/bejne/hnWcaWOpgLCrZqc which demonstrates how to use an external data source to simplify branching. Glad it's been helpful Douglas 👍

[@kfasekk]

this is so devilishly clever, I am beyond impressed. Helped me greatly, thank you.

[@DamoBird365]

Tell me how much faster your flow runs 😱👍

[@kfasekk]

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.

[@DamoBird365]

@@kfasekk amazing 👍 thanks for coming back and sharing. Really happy to hear it’s helped.

[@StephanOnisick]

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.

[@michelhegeraat5430]

Excellent. 👍 I did not know this lookup was possible. This will be very useful at times.

[@roachk9gaming315]

Awesome!!!!!!!🎉 I was using XML to achieve same results, but this is a much better approach. Thank you!!

[@geralddahl9159]

Thx for mentioning coalesce RE null values and json beautifier. GD

[@DamoBird365]

Coalesce is definitely a handy one to know about for that reason. Saves a complex nested if.

[@taaee]

Watching again and AGAIN it's wonderful! My only issue is that the second List (Let's say Order or Order details) has too much data and I need to filter it first to bring only order for Customers get item (which has fixed filter). I'm looking for some way without damage (Apply to each) all this beautiful flow!

[@nicholebrown5009]

This is awesome!!! Thank you so much!!!

[@63huggybear]

Fabulous, just what I needed.

[@PaulieM]

Clever idea 💡 Nice one ❤

[@piotrrusak]

Similar approach can be achieved using xpath inside Select expression - which one would be faster for large lists (what do you think @Damien / @Paul) ?

[@PaulieM]

@@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.

[@DamoBird365]

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.

[@StephanOnisick]

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)

[@DamoBird365]

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.

[@scootermcgavin7480]

this helped me immensely thank you!

[@DamoBird365]

Nice one, please tell me more. Was it efficiency you achieved or a better understanding of select?

[@scootermcgavin7480]

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

[@Stacey_work]

Very Helpful. Any hints on how to save this join to excel and filter?

[@DamoBird365]

You could watch this ? Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk

👍 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

[@DamoBird365]

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.

[@alecseidman5601]

Hi Damien, quick question - I'm trying to merge two lists from Sharepoint, my trigger is when an item is modified or created in the first list, and the second list is 2 columns, one unique ID and an email column. I'm trying to have the second table's email's appear as an option in the "Send an Email V2" operator but haven't had luck. Do you know if this is possible? Thank you!

[@basehumax]

Thanks master ♥

[@tibobago]

Thanks, great method. How do you apply it for more than 5000 records for a very Sharepoint list?

[@DamoBird365]

I’m not sure what you mean? You can use paging to get more than 5000 items and the logic is identical.

[@lee53311]

This is great, thank you!

[@stephank5777]

I want to join and then Update a „Master List“ with data from a „Child“ List - is that possible?

[@necdetsaritas8722]

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.

[@DamoBird365]

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.

[@binaat]

Where does the new array is stored? How can we create a new SP List by combining these 2 lists?

[@DamoBird365]

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.

[@karolinaunderwood1039]

This is fab! Thank you so much!

[@DamoBird365]

Thanks Karolina 👍

[@napoleonmachine1117]

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?

[@DamoBird365]

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.

[@saika_0420]

I have the same problem dealing with a 1 to many tables. The culprit is the json() expression where it omits duplicate key object, and the key object from your case is the jobNumber.

[@vlastahavranek9028]

@@DamoBird365 Same problem here. JSON call discards other items with the same key from the previous select. Details: I'm selecting assignees by their manager's email and want to group the assignees by manager email using your approach. It only gives me a single assignee for each manager, though.

[@adityadeshmukh3150]

Brilliant!! ❤

[@robofski]

Next level stuff!!

[@robofski]

I know it was a year ago I first watched this, but I finally had a need for it so rewatched it. It really is a great bit of teaching and if I think how I might have tried to do what I'm doing without knowing this (apply to each) it would have taken significantly longer. I've managed to get my array of 4000 items matched to another array and the items where there is a difference reported in minutes. 👍👍

[@DamoBird365]

Cheers @robofski appreciated. I am glad you had a chance to use it and thanks for letting me know. Makes it worth it 👍

[@kevinjvn5]

What if the object I want to build is a user object with a array field named orders and inside each order item there is a object field named order details. Is there a way to achieve this?

[@NinaVdA]

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?

[@DamoBird365]

What’s the error? Do you use a ? In your expressions? It should return null.

[@JoeSmith-np6nz]

is there a way to create a flow that allow me to merge rows within the same data set based on a duplicate field, say email for instance? please help

[@juanantonioperez1727]

Great!!!👏👏

[@DamoBird365]

Thank you! Cheers!

[@LeeladharGajulapalli]

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

[@yippiekyoo]

You can fix it by choosing a column with unique values in the select statement. :-)

[@MariusBeckermann]

Very helpful reply. Was just running into the same issue. This whole way of joining is just genius

[@DamoBird365]

Thanks Marius 👍

[@philipllorin6105]

super help.

[@DamoBird365]

Cheers Philip

[@Scott-lc5kh]

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.

[@DamoBird365]

Use range(0,length(your array)) as input to a select action and you can get each object where item() is the integer.

[@Scott-lc5kh]

@@DamoBird365 amazing. worked a treat. thanks 👍

[@DamoBird365]

Flipping awesome 👍

[@ukm365]

Hopefully I know what's coming. 😉

[@ukm365]

I didn't! Nice!

[@DamoBird365]

@@ukm365 what were you expecting?

[@ukm365]

@@DamoBird365 Oh, I was absolutely expecting the the selects, but you went over and above here. 🙂 Brilliant stuff!

[@DamoBird365]

Ahhh, I do love a select 😉🥳

[@ukm365]

@@DamoBird365 innit ... and with an xpath() inside? You're gonna have to give me some alone time! XD

[@AdamHaas]

For some reason all of the values I add from the second array all come up null. EDIT: I figured this out. You are merging two object arrays into another array, I only needed to merge one object into another array.

[@DamoBird365]

@@AdamHaas it’s because ? in an expression returns null if the path is not found. Most likely a typo in a key name or your expression isn’t quite right.

[@AdamHaas]

@@DamoBird365 I was converting my first array into an object needlessly. I just needed to make the second array in that format for the merge.

[@tylerkolota]

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. kzbin.info/www/bejne/omGxpoyqjcmWqLM I’m glad I can point people to this piece now.

[@DamoBird365]

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.

[@400_Labs]

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."

[@DamoBird365]

I am honestly not sure, you can share the expression or alternatively try the forum.

[@400_Labs]

Never mind. I figured out that I put in a semi colon instead of a colon in the select step. Duh! Brilliant work Damien!

[@klebermedeiros8742]

Very nice! But You should think to speak more slowly. People from other countries are watching you.

[@matthewdevaney3912]

You must have been watching at 1.5x. This video is not fast. Adjust the playback speed to 0.25x, 0.50x, 0.75x if needed.

[@Wzxxx]

What if initial array i empty? I would like to add another array and another and os on so the base array is being built up?

[@DamoBird365]

A union?