How to Compare Two Lists in Power Automate Without Apply to Each
Вставка
- Опубліковано 7 лип 2024
- In this tutorial, I'll guide you through a detailed demonstration of how to efficiently compare two SharePoint lists - a master list and a regional sales-based list. Learn how to identify and add any missing items from the regional sales list to the master list, comparing the two arrays without using the "Apply to each" action.
Intro (00:00): Begin your journey with an overview of what's to come.
The Lists to Compare (00:35): Understand the two SharePoint lists we'll be comparing and their importance.
Building the Flow (02:07): Learn how to build an efficient flow to handle the comparison and addition of missing items.
Primary ID Array via Select (03:02): Explore the creation of a Primary ID Array using the 'Select' function, an essential part of our flow.
Validating the Flow Output History (05:28): Dive into validating the flow output history, ensuring your flow is running as expected.
Creating Items (06:32): Discover the process of creating items in the master list from the regional sales list
New Designer Dynamic Value Bug? (07:37): Discuss a potential bug in the new designer dynamic value.
Validating Item 6 has been created (09:10): Verify the successful creation of a new item in the master list.
Running the Flow on some more items (09:37): Watch the flow in action on several more items, demonstrating its efficiency and scalability.
Outro (10:45): Wrap up the session with key takeaways and a look into what's next.
This tutorial is perfect for anyone dealing with large datasets, ensuring efficiency and preventing throttling or hitting API limits. Make sure to connect with me and subscribe to stay updated with more such useful tutorials!
Join the conversation on Twitter: damobird365
Follow for updates on LinkedIn: linkedin.com/in/damobird365
Explore more on the blog: damobird365.com
Want to watch more efficient videos ? Check out this play list • Power Automate Efficiency
Blog post- compare two arrays pnp.github.io/blog/post/compa...
Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕ - Наука та технологія
Your videos always help me take my flows to the next level. This is a great technique that I never would have thought of. Thanks for sharing!
Awesome work, Damien. was very useful for my work today
one of the biggest headache , thanks for your updated video :)
Great video. Thanks for sharing!!!
Great video. Your communication/teaching style is clear and easy to understand.
Cheers Brett, much appreciated 👍
Will make flows so much more efficient and faster… thanks!
THANK YOU! I have been working on this for 2 days and FINALLY have a reliable and efficient solution to update my excel tables.
Tell me the error and I’ll try and help. 👍 it will work with any data source. Promise 😉
You cracked it?! Nice one 👍
Another useful tutorial. You've got me not using "Apply to each" when I can do an expression. I recently do a flow to get all users from a SharePoint group, and at the end I did a Select for just the email and followed it by a Join with a semicolon--No Appy to each. You're a great teacher.
Nice one, your flows will thank you in the long run 😂👍
Thank you very much for this video. Here you are using one condition to compare two list, if we need compare with 4 fields then what we meed to do with out apply to each action.
I have a flow that needed to look at a data source with ~4000 rows and compare it to another data source to see what had been added/removed. Worked OK using an apply to each but took about 40 minutes to run and had started to be unreliable (often failed). Your previous blog on this subject made me redesign the flow and it now takes 60 seconds and just doesn’t fail!! I think every flow I have with a select action has been influenced by a video from you, how you use that action never fails to amaze me!! Awesome work!
Thank you. Very much. I hope others discover the benefits of using these techniques also and if you discover you’ve got an inefficient flow, and I’ve not got a video solution, drop me a note. It could be my next challenge.
I'm looking to do the exact same thing and it's taking 30 + minutes as I have a very large dataset. Which blog post are you referring to?
You are a genius. I thoroughly enjoy your videos. Thank you very much for your hard work.
Cheers Adhuma, I enjoy sharing ideas. I hope you get to try this out.
Good one!
Thank you!
Have you got a use case for this? 🥳
Dynamite! Thanks for another fantastic video. I´ll definitely apply this concept.
😂 thank you💥 let me know how much time it saves you in a flow 👍
Great video! Thank you! I work as an auditor and I send invoice confirmation requests by e-mail with an option for each item from a SharePoint list (Get Item). When the response is back, a new item is created on a separate result list. Some requests remain unresponded and now I know how they can be filtered using your method.
Excellent as usual. Here you compare one field with that same field in another list. It would be quite interesting if you compare multiple fields, or even an entire record. Ever done that? I'm not directly seeing how I can specify multiple fields in the right side of the Filter array.
I see it now :-) Just need to create two arrays with the same structure.
Let me know how you get on
@@DamoBird365
I did a get rows in SQL, used Select to get the columns I want in an array, a get items on SharePoint and second Select to get the same columns I want in a second array.
Then the filter has the array from SelectSQL as source. The Filter Query has the array from SelectSharePoint, ‘does not contain’ item(). 😊
Getting the data from the sources takes 10-20 seconds, getting the changed records array show 0s so far.
Thanks Damien.
Another useful trick, is if you have to use an apply to each (and you are using selects or filter arrays in the input), create a compose action and use the expression “item()”… then when you create a sharepoint item you dont have to write it like you are inputting an array… so it goes from “item()?[0]?[‘Title’]” -> “item()?[‘Title’]”
This is because the compose will only show the current item.
Thanks for the tip, can you explain? You would insert a compose inside the apply to each to get the current item of the loop? I am not understanding why this removes the need for integer indexes [0] which are not needed in the video but keen to understand.
Thank you for the video, works great. Any chance you'd do one on comparing 2 lists on multi person fields?
I'll take a note but maybe one to ask on the forum meanwhile powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums
I’ll be looking to make a sample using this method soon. But I’d like to To add or remove items. In one case a master list has it or not.
Would I then use a condition that if it’s missing to perform a delete item and if it exist to use the create? As it’s already filtered maybe I’d need parallel line for one that contains and one that doesn’t contain?
Thanks for the great videos and blogs. I have used your string searches for parsing emails. But will not be working to try and AI builder going forward.
I think you've cracked it - a filter with contains and another does not contains and then deal with both filtered arrays as appropriate.
This is class!!
I have 2 list one is excel, the other is a MS list, the excel is a download from a larger data warehouse and APIs are not allowed.. so stuck with download only, this is where all the changes happen/updates.
i want to run a flow to update the changes to the MS list.
There is column that never changes on the Excel list.
I do have a flow but it takes an ages and is not efficient at all.
open to suggestions
There’s probably two areas to improve. Identify the items that are new per the video and look at sending a SharePoint batch request to insert items into a list. If you’re trying to spot changes, you can concat the fields in a select and check if that array contains a concat via filter array.
Hey,
thank you a lot!
I have a question.
I have sharepoint list with about 500 entries, each entry contains a user-mail.
Now i have to get the userprofile for these people.
I allready made it so far, that i save all these mails in an array and loop the "get-user"-action through that to recieve all the profiles.
But when i want to update the items in my sharepoint list, it allways makes an apply-to-each-loop out of it. I understand why, but I dont now the way to change that.
So that all the items get updated with the recieved user-profile-data i got.
I’m not understanding your use case. Can you post more details to a forum? This video might help, might not 🤷♂️ ua-cam.com/video/d99Rr5djcME/v-deo.htmlsi=8Eb42cw7werifzi-
Scenario where we're wanting to filter a v large list (as in hundreds of thousands of items) by a v small list (find these few IDs in that v long list), would there be a method of inserting the select from the small list in the odata filter on the v large list?
Same method. Turn the small list into an array of ids with a select, then filter the large array, where the output of select contains the item()?[‘id’] or equivalent id key name. Those ids in the output of select will be returned from the large list.
@@DamoBird365 fantastic - thanks
Is this possible with two Excel files? When I try to add the filter array it just creates an apply to each. I have a working flow using 2 Apply to Each but I am already getting told off about API calls :(
Definitely possible with two excel files. You’ll need to understand why an apply to each is inserted. This might help ua-cam.com/video/d99Rr5djcME/v-deo.htmlsi=lEtNzKgKtBqo6_Q5. Good luck 👍
Hi guys could you help me why if I want to compare two columns for example: Transaction id and Customer Name?
You could concat the two columns to form a string in the select and then compare the concatenated string.
I am working on a similar solution, however the list of items that needs to be added to the master contains some duplicated identifiers, how would I remove these duplicates before creating an item to add to existing list, to reference this video, if there was two instance of TXN006 in SalesNorth how would the duplicate be removed before adding to the master list ? thanks
You can use union to remove duplicates. I demo that here Send Unique Emails with HTML Table using Data from Excel #PowerAutomate
ua-cam.com/video/5msxh5Ux8DY/v-deo.html
@@DamoBird365 Thanks, I managed to use a compose and union expression and my power automate is now working 🎉🎉 thank you for the videos
May have already been answered somewhere but how would I implement this to compare two lists that both contain a field EmployeeID as the unique value, what i then want to check is each field alongside it the same, i.e. FirstName, LastName, JobTitle and then if any of these fields are different in the master list just update that field rather than the whole row
You could probably use a concatenation to spot the difference, I’ve not covered this before.
This is the video I was looking for comparing, however, I have two Dataverse tables in my situation instead of SharePoint but I am getting all the rows back from 2nd table including the matching ones. Any tips?
Your condition in the filter must be evaluating true for all rows. Worth taking a step back and looking at history. Let me know how you get on. Have you somehow compared the table against itself?
@@DamoBird365 Yes. These are small dataverse tables that I am testing before deploying to prod, especially the 2nd table that has only two rows - 1 has matching row but the other doesn't. I manually tested in dataverse master table using filters as and I made sure its the same value (copy and pasted from one table to other). I have exact same cloud flow steps as your but I keep getting both rows in filter array output.
In fact, I have the same use case as you shared. after comparing, I have to add rows to master datverse table. :)
@@Salstravels you could post details on the forum?
@@DamoBird365 Thanks, I appreciate it. I'll comment in the blog post link in the description. Feel free to share the link of the forum if you have a preference.
I have 4 flows for doing 4 reports depending on the options selected in a microsoft form, I tried using switch conditions but have trouble doing for each within each as it has multiple items it does for each option
Can you share more details on the forum? I don’t understand your requirements.
I am trying to replicate same for each trick but it creates blank entries in my sharepoint list
How to utilize this efficient style process flow when you have two SharePoint lists where they match on the values of the ClientName column and you need to update the email address in SP list 1 from the email address in SP list 2? I keep trying to use Select but PowerAutomate keeps nesting my steps in Apply for each.
Hi, check out ua-cam.com/video/d99Rr5djcME/v-deo.html to understand apply to each. Most likely you are selecting the wrong dynamic values and bringing a table of data into an existing select. I'm sure you will crack it 👍
In the Filter Query, I attempted a different approach, Title doesn't contain in Output, that didn't work. Thank you for sharing.
That’s the wrong way around. A string cannot contain an array.
PowerApps: "b" in ["a","b","c"]=>true
Hey damo
Im trying this but my lists are in two different excel files, and when I use the select it keeps giving me an "Apply to each" how should i do this? I want to look for the two lists because one is updated everyday automatically, and i want to update the list in the other file
I would start again. You’ll have chosen the wrong dynamic values to create the apply to each. There’s also a blog post in the description that might help.
@@DamoBird365 yes it really worked. Now i wanna do the same process but the thing is that i have to do it using an xlsm file. How can i do it if i cant see the name of my columns?
@manuelmacedo3211 I don’t think a cloud flow can read xlsm? Maybe it does 🤷♂️ glad you got this working though 👍 have you seen that you can use graph api or office scripts to create and therefore read excel without a table? ua-cam.com/video/gtlklzi6MDg/v-deo.htmlsi=FAX_C-bfliXyYFRr
Hey Damien, any idea why my select function output is [ ]?
I set it up in the same way like you, but somehow it is not working.
Thank you 😊
Is your input also an empty array? It would mean your expression is returning null, probably a typo in the name in brackets.
Hi Damien, unfortunately, my filter array action output is just "body [ ]" and not giving me the desired values which are not in the other list. Any idea why this can be a reason?
You’ll need to write an expression. The video should explain this. Or alternatively check out the blog post in the description 👍
@@DamoBird365 Thank you for your reply. I tried with the blog post and also the video. And I am not sure why my the output of the "Filter Array" is empty. Thank you anyway.
@@arpankalothia164 it will be empty because the condition in the filter is always false. Check the input history. Can you see the name of the value you are filtering on? Check this in the expression when editing.
@@arpankalothia164i am facing this problem too, did you fix it now ? Thanks!
@suinianlu1142 check out the blog post pnp.github.io/blog/post/comparing-two-arrays-without-an-apply-to-each/ see if this helps
Due to the limitations of the number of columns in planner. I have to concatenate my transaction ID# and Description together in the planner Title to form a title such as 12345 - Description Name. If the array is just based on text values let's say array 1 from excel is ["12345","12346"] and the array 2 from planner contains ["12345 - Description1","12346 - Description2"] the filter array returns [] when using Contains. Is there a solution for that?
Could you split on - and get first()? You might need to use trim() to remove spaces also. Trim(First(Split(item(),’-‘)))
@@DamoBird365 BEAUTIFUL!! Yay it worked. Thank you very much for posting all the videos. It has been very beneficial.