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

Подписаться 12 тыс.

Просмотров 13 тыс.

50% 1

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 ☕

Наука

Опубликовано:

2 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 73

@cq401147 9 месяцев назад

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

@DamoBird365 9 месяцев назад

Tell me what it’s helped you achieve 👍

@paulwagstaff8349 8 месяцев назад

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 8 месяцев назад

Thanks Paul.

@StephanOnisick 10 месяцев назад

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

@DamoBird365 10 месяцев назад

Cheers Stephan, Thanks for your kind words.

@michelhegeraat5430 Год назад

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

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-PD980sKKx0E.html which demonstrates how to use an external data source to simplify branching. Glad it's been helpful Douglas 👍

@StephanOnisick 10 месяцев назад

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.

@kfasekk 5 месяцев назад

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

@DamoBird365 5 месяцев назад

Tell me how much faster your flow runs 😱👍

@kfasekk 5 месяцев назад

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 5 месяцев назад

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

@nicholebrown5009 4 месяца назад

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

@robofski Год назад

Next level stuff!!

@adityadeshmukh3150 Год назад

Brilliant!! ❤

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

@lee53311 Месяц назад

This is great, thank you!

@basehumax 11 месяцев назад

Thanks master ♥

@scootermcgavin7480 9 месяцев назад

this helped me immensely thank you!

@DamoBird365 9 месяцев назад

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

@scootermcgavin7480 9 месяцев назад

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

@karolinaunderwood1039 2 месяца назад

This is fab! Thank you so much!

@DamoBird365 2 месяца назад

Thanks Karolina 👍

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

@juanantonioperez1727 Год назад

Great!!!👏👏

@DamoBird365 Год назад

Thank you! Cheers!

@philipllorin6105 Месяц назад

super help.

@DamoBird365 Месяц назад

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

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

@StephanOnisick 10 месяцев назад

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 10 месяцев назад

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.

@necdetsaritas8722 6 месяцев назад

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 6 месяцев назад

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.

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gtlklzi6MDg.html

@tibobago 6 месяцев назад

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

@DamoBird365 6 месяцев назад

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

@napoleonmachine1117 5 месяцев назад

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 5 месяцев назад

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.

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

@NinaVdA 2 месяца назад

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 2 месяца назад

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

@user-ed2qp2ur1g 10 месяцев назад

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 10 месяцев назад

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

@Scott-lc5kh 9 месяцев назад

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 9 месяцев назад

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 9 месяцев назад

@@DamoBird365 amazing. worked a treat. thanks 👍

@DamoBird365 9 месяцев назад

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

@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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-l0NuYtXdcrQ.html 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.

@Tarick_Arnold Год назад

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.

@Tarick_Arnold Год назад

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