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 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.
@@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
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
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!
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!
All of your videoas are wesome, and extremely useful! Very tightly presented - every second counts. I have to view it multiple times to really get it. (BTW, at 6:52, I think you meant to say "colon" not "semicolon").
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 th-cam.com/video/PD980sKKx0E/w-d-xo.html which demonstrates how to use an external data source to simplify branching. Glad it's been helpful Douglas 👍
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.
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.
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.
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
You could watch this ? Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate th-cam.com/video/gtlklzi6MDg/w-d-xo.html
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!
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.
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.
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.
@@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.
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. 👍👍
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!
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?
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?
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.
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
HHello Damien. Very good workaround to the ApplyTo Each. I still have a problem.. In my case I only have 2 arrays one of them can have repeated items for the same Id; then on the last step, when I get a json object all the items with repeated Id are lost
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.
👍 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.
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.
@@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.
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."
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. th-cam.com/video/l0NuYtXdcrQ/w-d-xo.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.
I can't express how helpful it is in words!
Tell me what it’s helped you achieve 👍
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!
@@unolaugh thank you 🙏
@@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.
Thank you, you saved my life.
Power Automate is so limiting and frustrating but you found a clever solution.
Thanks Isa.
@@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
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
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!
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.
All of your videoas are wesome, and extremely useful! Very tightly presented - every second counts. I have to view it multiple times to really get it. (BTW, at 6:52, I think you meant to say "colon" not "semicolon").
Great, great vid Damien! With a bonus tip on JSON beautifier! Awesome! Thank you so much!
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 th-cam.com/video/PD980sKKx0E/w-d-xo.html which demonstrates how to use an external data source to simplify branching. Glad it's been helpful Douglas 👍
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.
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.
Excellent. 👍 I did not know this lookup was possible. This will be very useful at times.
This is awesome!!! Thank you so much!!!
Awesome!!!!!!!🎉 I was using XML to achieve same results, but this is a much better approach. Thank you!!
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.
Fabulous, just what I needed.
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
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.
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
th-cam.com/video/gtlklzi6MDg/w-d-xo.html
This is great, thank you!
I want to join and then Update a „Master List“ with data from a „Child“ List - is that possible?
Thanks master ♥
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!
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.
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.
This is fab! Thank you so much!
Thanks Karolina 👍
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.
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.
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.
@@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.
Next level stuff!!
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. 👍👍
Cheers @robofski appreciated. I am glad you had a chance to use it and thanks for letting me know. Makes it worth it 👍
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
Not something I have a video on but it would be possible, albeit updating excel in bulk is not as easy as reading.
@@DamoBird365I’m trying to merge rows on sharepoint data list based on a single field. Is it possible ?
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!
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?
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.
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.
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. :-)
Very helpful reply. Was just running into the same issue. This whole way of joining is just genius
Thanks Marius 👍
Brilliant!! ❤
HHello Damien. Very good workaround to the ApplyTo Each. I still have a problem.. In my case I only have 2 arrays one of them can have repeated items for the same Id; then on the last step, when I get a json object all the items with repeated Id are lost
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 👍
👍 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.
super help.
Cheers Philip
Great!!!👏👏
Thank you! Cheers!
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.
@@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.
@@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.
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!
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.
th-cam.com/video/l0NuYtXdcrQ/w-d-xo.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
Very nice! But You should think to speak more slowly. People from other countries are watching you.
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.
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?
A union?