How to Compare Two Lists in Power Automate Without Apply to Each

[@aaragon0902]

THANK YOU! I have been working on this for 2 days and FINALLY have a reliable and efficient solution to update my excel tables.

[@DamoBird365]

Tell me the error and I’ll try and help. 👍 it will work with any data source. Promise 😉

[@DamoBird365]

You cracked it?! Nice one 👍

[@WelcometoTarkov-yu4ux]

Awesome solution! Took my terrible 1.5hr Apply-to-each solution down to 3.5 minutes. Thank you for making this video!

[@robofski]

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!

[@DamoBird365]

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.

[@markdelbiondi]

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?

[@morris5984]

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!

[@brettwoodward]

Great video. Your communication/teaching style is clear and easy to understand.

[@DamoBird365]

Cheers Brett, much appreciated 👍

[@StephanOnisick]

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.

[@DamoBird365]

Nice one, your flows will thank you in the long run 😂👍

[@sharondleisk]

Thank you ! I had a similar scenario and I was burning time trying to get a solution that wasn't a nightmare of nested Apply to each actions. I was using Dataverse and needed to find any deleted records from a previous version of a load to the current version. Your video got me up and running and I now have a neat, quick solution :)

[@friendlyfriesen]

Will make flows so much more efficient and faster… thanks!

[@shivabkumar7999]

Awesome work, Damien. was very useful for my work today

[@sweetmarch9th]

Really helpful! Thanks 🙏 Should do how to compare more than two and how to print the result too 😬 hahaha

[@filipwinski4219]

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.

[@radhakishansharma3582]

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.

[@adhuma2000]

You are a genius. I thoroughly enjoy your videos. Thank you very much for your hard work.

[@DamoBird365]

Cheers Adhuma, I enjoy sharing ideas. I hope you get to try this out.

[@AlysonVeras]

one of the biggest headache , thanks for your updated video :)

[@SpencerVinson-v4z]

Works great and really efficiently, but I would like to add that if your ID fields are values it doesn't want to give results in the filter, the workaround I used is just concatenating "ID" at the beginning of the value to have matching strings.

[@DamoBird365]

By values do you mean integers, rather than strings? Both would have to match. So by adding string ID to an integer you’ve created a string. You could also use string(‘12345’) learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#string

[@mygardenexperiment]

Thank you for the video! I had to convert my ids to strings for this to work. Hopefully it will save someone else some time.

[@extraktAI]

Thanks for this! The apply to each control has become somewhat of a mortal enemy of mine ❌😂

[@DamoBird365]

@@extraktAI nice, hopefully you are reaping the rewards with efficiency 👍

[@kavingaranaraja4195]

Great video. Thanks for sharing!!!

[@stefankirst3234]

Dynamite! Thanks for another fantastic video. I´ll definitely apply this concept.

[@DamoBird365]

😂 thank you💥 let me know how much time it saves you in a flow 👍

[@accreditationquality681]

I really like how you explain things. I tried to use this to compare two lists as described but the final step I need is then to be able to delete a duplicate entry on one of the lists. So look at list 1, compare with list 2, if an item on list 1 appears on list 2, delete from list 2. Any help gratefully received.

[@michelhegeraat5430]

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.

[@michelhegeraat5430]

I see it now :-) Just need to create two arrays with the same structure.

[@DamoBird365]

Let me know how you get on

[@michelhegeraat5430]

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

[@MarcusCrutchfield]

Thank you for this video. It definitely is way more efficient! Is there a way to look up 2 fields? Using your data if you want to look up Product Sold and Sales Person name and compare both fields in each list?

[@DamoBird365]

Sounds like a challenge. You could concat them maybe? I’ve not tried comparing objects but it might work. Let me know what you find.

[@MarcusCrutchfield]

@@DamoBird365 In the SELECT step, I used CONCAT to combine the 2 fields. It worked perfectly. THANK YOU!

[@pierrickdefossez3157]

thank you, it works like a charm. using this technique, is it possible to select two fields of the sharepoint list and filter array on two matching fields? e.g.: I want to check if the combination name + surname already exists in the master list?

[@WayneFoster-xy2ii]

Really like this solution! Trying to apply the same logic to 2 excel files with tables matching on project name for the compare but the matching doesn't seem to work as it brings back all records, any thoughts?

[@WayneFoster-xy2ii]

Ok I was able to figure it out! Your videos are a huge help to me and my team!

[@Salstravels]

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?

[@DamoBird365]

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?

[@Salstravels]

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

[@Salstravels]

In fact, I have the same use case as you shared. after comparing, I have to add rows to master datverse table. :)

[@DamoBird365]

@@Salstravels you could post details on the forum?

[@Salstravels]

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

[@davidclay717]

Thank you for the video, works great. Any chance you'd do one on comparing 2 lists on multi person fields?

[@DamoBird365]

I'll take a note but maybe one to ask on the forum meanwhile powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

[@garcialex06]

Good one!

[@CaelanGill]

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?

[@DamoBird365]

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.

[@CaelanGill]

@@DamoBird365 fantastic - thanks

[@christophvanderwerff5718]

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.

[@DamoBird365]

I’m not understanding your use case. Can you post more details to a forum? This video might help, might not 🤷‍♂️ kzbin.info/www/bejne/mmqcg6Vrmc-Wg6csi=8Eb42cw7werifzi-

[@evanmaclean943]

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.

[@DamoBird365]

I think you've cracked it - a filter with contains and another does not contains and then deal with both filtered arrays as appropriate.

[@garaymr]

I love you lol, thanks!

[@DamoBird365]

😂 thank you, I’m glad this helped 👍

[@ExplorerheNorth]

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

[@DamoBird365]

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.

[@TheCarter117Gaming]

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.

[@DamoBird365]

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.

[@therealmattbyers]

I spent very close to 6 hours today trying and trying this, following everything to a tee, attempting various other methods, I was getting so frustrated thinking what is it that i'm doing wrong. No matter what, it was showing "No outputs" result. Turns out that even though the 'OUTPUTS' window was showing "No outputs" text, in final defeat I clicked on the 'Show raw outputs'... and there were the results.. it has been working the whole time but the Output window wasn't showing it. Double check the 'Show Raw Outputs' everyone!!

[@DamoBird365]

@@therealmattbyers new designer? I may have seen this recently too.

[@ElizabethGrzybowski]

Hi! I keep getting a null value in the body of my filter array even though there are matches. In one of my SP lists the unique id is single text and in the other SP list it is an integer. Is that the problem? How do I convert an output of integers from a get items to an array to be able to work with it? (If that's actually the problem :) )

[@DamoBird365]

@@ElizabethGrzybowski you’ll see in history an integer wouldn’t have quote around it vs a “string”. Ie 123 vs “123”. There is an expression learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#string that might help?

[@anujpathak1817]

I am trying to copy the same steps as your video but in filter array condition when I select my EmployeeID column to compare it with the output of select it converting it as apply to each loop on its own . Please help

[@RiskManagement-ex2nl]

--- this is a great idea but my filter array output is still blank - I'm using this to find matching data across two Excel sheets and even though the input shows there's a match the filter array output is still embedded in a JSON as a blank array { "body": [] }

[@DamoBird365]

@@RiskManagement-ex2nl you won’t have set it up correctly. The the input contains data and the output is blank, your condition is wrong. Have you correctly formatted the select to the values you want to check are contained within? Is there a typo in your second expression? Try with and without a ?. If you get an error without ? You most likely have a typo.

[@RiskManagement-ex2nl]

@@DamoBird365 Wow! thank you for the quick reply. I think the main problem was I tried using 'equals' in the filter array between the outcome of 'select' and 'title' instead of 'contains' which gave me the result I was looking for

[@DamoBird365]

@RiskManagement-ex2nl glad you’re sorted 😉

[@Felix-zx3bp]

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

[@DamoBird365]

You can use union to remove duplicates. I demo that here Send Unique Emails with HTML Table using Data from Excel #PowerAutomate kzbin.info/www/bejne/a57WqZtrit1rers

[@Felix-zx3bp]

@@DamoBird365 Thanks, I managed to use a compose and union expression and my power automate is now working 🎉🎉 thank you for the videos

[@Ohiomaprime]

Thank you!

[@DamoBird365]

Have you got a use case for this? 🥳

[@roycinta3731]

Hi guys could you help me why if I want to compare two columns for example: Transaction id and Customer Name?

[@DamoBird365]

You could concat the two columns to form a string in the select and then compare the concatenated string.

[@SirThomasR]

My heads going to explode figuring this one out. I am trying to do something similar with an excel list and SharePoint list. I am trying to get ID numbers from the two (excel is the master) and if there is a match then look at the email addresses from the matched IDs only and if excel has a different email than my SharePoint one update it. How in the world do I accomplish this?

[@manuelmacedo3211]

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

[@DamoBird365]

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.

[@manuelmacedo3211]

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

[@DamoBird365]

@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? kzbin.info/www/bejne/naXPnJ-wnpuAesksi=FAX_C-bfliXyYFRr

[@cindymitchell0614]

Could this be tweaked to perform a lookup function between excel and SP list? I'm trying to figure out a way to pull in general ledger coding from excel to sharepoint, based on the location in sharepoint.

[@DamoBird365]

Are you wanting to perform a lookup on a ledger code based on a string? What’s the end result to look like? Sounds interesting.

[@cindymitchell0614]

@@DamoBird365 The sharepoint list is fed from a MS form for refund check requests from another flow. Everything is populated in the list from the form, except the GL Location column, it is entered manually by the person that verifies the request. The location is always 4 characters of text, consisting of a region and store location (e.g. AL1B, CF66, EFDB, etc.) and each location has a general ledger code, consisting of the same region and store location, but also the state the store is located in, we use for posting in our Accounting software (AL1B would be AL-1B-AL, etc). The excel file has a master location table with their applicable general ledger code. I want the flow to trigger once the GL Location field is updated for the sharepoint list item, lookup that location in the master location table of the excel file and update the sharepoint list item with the general ledger code in an adjacent column.

[@cindymitchell0614]

@@DamoBird365 The common value between sharepoint and excel is the location, which is always 4 text characters. The excel file has a master location table with the applicable general ledger code for each location. I want the flow to lookup the location from the sharepoint list item in the excel file and update the list item with the general ledger code in an adjacent column.

[@DamoBird365]

@cindymitchell0614 it can be done, absolutely. Are you triggering this flow per item? Or on multiple items? If it’s when an item is created you could just filter the excel file. You could also trigger the flow from a button on the item. Lots of ideas with this. You could also just do a lookup via calculated column if the excel was another list.

[@cindymitchell0614]

@@DamoBird365 The way I have it built now (which isn't working) is its triggered when an item is created or modified. Then Get Items from sharepoint list -> List Rows Present in Table -> then a nested condition to match the locations between the list and excel within 2 Apply to Each controls. The master excel file has over 3000 lines and would need to stay in excel since its used for vlookup formulas for other processes in excel.

[@moyura2]

In the Filter Query, I attempted a different approach, Title doesn't contain in Output, that didn't work. Thank you for sharing.

[@DamoBird365]

That’s the wrong way around. A string cannot contain an array.

[@moyura2]

PowerApps: "b" in ["a","b","c"]=>true

[@reviewassistant6891]

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

[@DamoBird365]

Can you share more details on the forum? I don’t understand your requirements.

[@onionman2117]

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 :(

[@DamoBird365]

Definitely possible with two excel files. You’ll need to understand why an apply to each is inserted. This might help kzbin.info/www/bejne/mmqcg6Vrmc-Wg6csi=lEtNzKgKtBqo6_Q5. Good luck 👍

[@davidakridge2831]

Theoretically, can this be used with contains to update a records instead of create?

[@DamoBird365]

@@davidakridge2831 instead of an apply to each with a condition? Yes. 👍

[@davidakridge2831]

@DamoBird365 I was thinking instead of does not contain in the condition, using contains giving me a list of those to update, but what ID do I use in selecting the record to update. Should i pull in title and ID in the previous steps? I would love to use this to create and update from one list to another.

[@crunchynoodles4062]

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?

[@DamoBird365]

Could you split on - and get first()? You might need to use trim() to remove spaces also. Trim(First(Split(item(),’-‘)))

[@crunchynoodles4062]

@@DamoBird365 BEAUTIFUL!! Yay it worked. Thank you very much for posting all the videos. It has been very beneficial.

[@arpankalothia164]

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?

[@DamoBird365]

You’ll need to write an expression. The video should explain this. Or alternatively check out the blog post in the description 👍

[@arpankalothia164]

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

[@DamoBird365]

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

[@suinianlu1142]

@@arpankalothia164i am facing this problem too, did you fix it now ? Thanks!

[@DamoBird365]

@suinianlu1142 check out the blog post pnp.github.io/blog/post/comparing-two-arrays-without-an-apply-to-each/ see if this helps

[@katcol312]

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.

[@DamoBird365]

Hi, check out kzbin.info/www/bejne/mmqcg6Vrmc-Wg6c 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 👍

[@Aniwe-c8u]

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 😊

[@DamoBird365]

Is your input also an empty array? It would mean your expression is returning null, probably a typo in the name in brackets.

[@iraitzgoikoetxea9021]

I am trying to replicate same for each trick but it creates blank entries in my sharepoint list

[@tskelly89]

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

[@DamoBird365]

You could probably use a concatenation to spot the difference, I’ve not covered this before.

[@Wzxxx]

This filter method doesn't work. Tried 100 times. I have 2 lists, each one has one column, no keys, only values but it does not work.