How to Compare Two Lists in Power Automate Without Apply to Each #PowerAutomate #efficiency

hi there folks in today's demonstration is all about comparing two lists in power automate the most efficient way possible so if you've ever tried to compare two lists looking for those that either match or for those that are missing you might have ended up in a really big mess with lots of apply to each actions and a very inefficient flow and potentially over time a flow that starts to fail well today I'm going to show you how you compare two lists without an apply to each using a select and a filter array so if that's something interest you please make sure you like And subscribe and without further Ado let's jump into the demonstration so I'm going to kick things off from my SharePoint site I have two lists the first of which is my master sales list and I have transactions from one through to five I then have a second list which is my regional sales for the North and I have transactions 1 2 and six and we can already see that uh my master sales list is missing transaction 6 so what I'd like to be able to do is compare the sales North against the master sales to see what transactions are missing and therefore create them in that Master sales but if I drag out the list so they're side by side and we think about the logic what you would tend to do in power automate is via an apply to each You' get the items of both lists and then with our regional Sales North for each of those items in an apply to each we would check to see does transaction one exist in any of these items here if it does with a condition then you would say do nothing if it doesn't then you create an item and then we do the same with two does it exist if it does we obviously do nothing if we don't it's we create it and then we look at number six and we can see number six doesn't exist so therefore we create it and this is actually a really inefficient way of comparing data in power automate and what you'll find is as those lists grow your flows will grind to a halt and potentially you'll hit API limits and your flow will start failing so my particular solution I'm not going to use an apply to each at all and you'll see these techniques through a lot of my videos that I demonstrate on my channel jumping over onto power automate you can see I have a very simple flow I've got manual trigger you may well well want to run this once a week you might have a recurrent so it does it once a day once every hour um but for this I have the manual trigger and then I have two get items so get items from the master which is from my SharePoint site from the master sales list and then I have the items from sales north and again with the sales North is the list name the only other setting that I've changed here is you'll see I've got limit entries to folder I've got a forward slash in there and that is I want to say a little hack but basically a little tip if you want to suppress the warning error that you get to say that you may bring back more items than expected but you're confident that you want to bring those back you can put the forward slash um of course also if you have lists that go beyond 5,000 you might want to come into the settings here and turn on pagination and bring across up to 100,000 items but that aside what we want to do now is we want to get all the primary IDs which in this case is a transaction number and I can do that using a select action so the select action will allow us to take an array in this case we want to get all of the master sales IDs so I can get the value which is an array and then I want to just purely return those IDs in an array so we can turn on this text mode here go into the the dynamic values and we can hunt down title in this case because that is my transaction number but make sure you go to the right to get items so I'm getting items from Master if I was to choose it from the wrong one you would probably find yourself in an apply to each and need to delete that select and start again so that will now give me an array of all of the transaction numbers and the great thing about that now is we can start comparing the sales North array against this array of transaction numbers do they exist and we can do that using a filter array action so think traditionally you do this when apply to each in a condition you're actually checking each one off individually against a large data set whereas this filter array will allow us to take the uh value array from our sales north and with that we have our filter query which is just a condition so we want to see if the output from our select which is now our array of the transaction numbers from our Master list does not contain the title or transaction number in our sales North so again if I make sure I get the title from Sales North if this condition is true it will return the item from the list and of course we have our new array from the select which will contain all of our transaction numbers one through to five and then we're going to compare that to see does it not contain the title in this case a transaction number so if I bring up that list on screen here through each of these items it's going to check does the array of transaction numbers 1 to five does it not contain one well it does so that's not true does it not contain two it does that's not true does it not contain six it doesn't so that's true therefore the filter array will only return transaction number six in that output from the filter so let's give that a save and test so with the flow complete the two things I really want to highlight are first of all the select action you can see we've taken the input from the master list we've turned that into an array of just transaction numbers the one through to five and then if I go into my filter array and we have a look at the input we can see that the input was from our sales North which includes our transactions one and then there'll be uh number two and they'll hopefully also be number six so these are the inputs if we look at the output we can see that we just have transaction six because that's the only one that didn't meet that uh condition or it's the one that did meet the condition in fact it it returned True by saying that this select array did not contain the transaction number six which equals true we therefore return in the filter array output just that one transaction number so you can see if we had a significant number of items that we're doing this comparison on we're using a single filter array in order to determine which of those items are missing so back into edit what we really want to do now is to create an item and uh as easy as that we want to create item or transaction number six if we choose create item instinctively I want to go and choose the site and the list for the master sales and then of course if I was to go and populate the title and the customer name Etc I'm going to end up in an apply to each and that's actually not a bad thing because I'll show you in a minute we can update the source of the apply to each to be our filter array rather than the get items so if I go into Dynamic content and I choose the title for our sales North you'll see We've Ended up in a 4 each Loop and I'll go through and choose the dynamic values now just for the other items that I want to include so product name and salesperson name and also the date now the important bit is the data source so the data source is automatically assumed we want to use the get item sales north but in fact we fil Ed using that filter array and we only want to create that one item so we can remove that Dynamic value that's been inserted for the four each and then we can replace it with an output from the filter array um and the this case I see we have body list of items which doesn't look familiar and if I click on it and go into the code View and have a look at this particular expression we see here I would say this is not correct um and this is possibly a bug in the new designer what I'd hope for for is the body of the output filter so I'm actually going to copy this and contrl + C I'll go into parameters I'll scrap this expression go into the Expression Builder and type in body and that will get us the output or the body from the filter array which will then allow us to Loop through it and create all the items we'll hit add there so the thing to note all these Expressions that have been created if we look at the code view here are all based on the name of the 4H Loop so the items are for each and you can see it calling out these different field names the title the customer name product sold you can write these things manually but because we've used this little trick we create item we've dropped the dynamic content in it's added in these expressions for us and then all we need to do is update the data source in this case the body of the filter array and so if I save and test that what I expect now is for rather than to create items from all the sales North we'll filter that sales North based on that condition we created and then it will only Loop through the one transactions that have been created in the filter and create that single item so the flow complete again if we have a look at that filter array and look at the outputs we know from before that we just had that one transaction number transaction number six and if I look at the for each loop I can see it's only looped one despite us initially setting it up to create items from the sales north but because we've changed the data source to the filter array it runs once it creates that item for for transaction 6 and of course if I jump over into my list and do a we refresh you can see that that item has now been created if I update my regional Sales North to include a few more transactions you can see I've got 99 304 and 893 jump back onto my flow and if we test and run it we can once more pop open in that filter array and if we look at the outputs we can see that transaction number 99 has been returned we have transaction 304 and we also hope hopefully have transaction 8893 which therefore results in the apply to each running three times and creating an item for each of those based on that filter array data so as you can see an extremely efficient way to compare two lists to see what's in one but not in another and as it happens it's something I've blogged on in the past about 18 months ago or so showing you this method and also showing you some of the time saving I can see here that at the very bottom I ran through a 100 records through this solution took 40 seconds with an in nested apply to each but when using the filter array method and the select to repurpose that array it's happening in a number of split seconds

Channel: DamoBird365

Views: 3,768

Keywords:

Id: 4IphRdADJBc

Length: 11min 6sec (666 seconds)

Published: Sun Dec 17 2023