PowerAutomate: How to compare between 2 excel files items and get the matching result

hello everyone okay so what we are going to do today is I am going to show you how we can get data from one file and compare it with another file right what we can do is we can use apply to each to iterate through the entries of one file with respect to the other and get the matching entries what we want right so this is one approach whereas the second approach would be to use a select and filter array right so I'm going to show you both the ways so that we can compare that which one is better for us to use right so let's get started so get serial number okay when a file is created SK this is deprecated created properties only Okay so see this is the document Library so these are the two folders that we have users file is where we will create the file or upload the files and the master file folder that you see in here we will have the master file from where that the file from the users folder we will have the comparison between the 2. so in within the users file I'm creating this see this serial number is something that we will match with the master file so serial number some random names what is important is that we put this in a table okay so this is important step because in the table design if you see this table one right this is something that we are going to use in the flow we are going to get this entries in the flow from this Excel and how we can going to list them is we are going to use the tabular format and the table content within it right so it needs to be in a tabular format even though user is uploading or creating that file that file has has to have a format and that is the tabular format okay so we have our file let's close this okay and let's create a master file for us to compare with okay serial number state City will have some random values so we have three serial numbers mentioned in there let's match two of them one two three four five and one two three four six seven we want two three four seven one two three four eight one two three four nine okay let's have five entries stay so again we will put this into a table as well and we can see that by default it is stable one all right now we have our Master file okay let's name this as Master file and we have a file from the user so if you can see five six seven one two three four five six this serial number this three serial number we have the starting three serial number so this is what we want to match and get the corresponding values in our file okay so this is the flow our objective is let's so whenever the user uploads the files now it creates we will have the flow will be triggered so the users folder is the one where the file needs to be created okay so this is our trigger now what I want to do is I want to create a new file in which the matching entries should be there okay so let's create one more here by the name of required file or the matching files okay so the user file will match with the master file and we will have our output in the matching file so let's create a matching file as well okay in the matching file so matching file will be created on the go so let's have one basic format that we want serial number state City okay thank you so we will put this in table format and design table one all right so why I have created that is we are going to create our file dynamically okay so for that let me so we will use get get file content and we will get the output of that file all right share documents matching file there it is save and let's trigger okay now what we have to do is we have to take this output and we are going to put that into compose and there we have it okay so let's create our file okay create file let's take the side edges the folder path we want to create it in our matching folder name of the file let's give it a dynamically file content we will use the output from the compose so this is this will create a dynamic file all the time with the format that we have suggested right so this will be a matching file now we can delete from the matching folder the file that we created we just needed the format output okay so now we have our trigger we have the matching file that will be uh created now let's move to the next step which is the actual let's list both our files Okay so list we will use this list rows present in a table let's take the user's file first so the user's file will be in the document okay so the file is not yet uploaded will be uploaded on the go so we will have and the file is created we will use that entry and we will have use the ID from that one and we know the table is table one okay so you would have to use table 1 has a custom value okay now the same for so this one is for the user's file okay so now let's close present in the table again test side three document Library and so this file exists there so we will use this is our static file this file will be present right users file will be a dynamic file the user whenever uploads the flow will trigger so we can pick it from there and the file that we are generating the matching file is a dynamic file but this one is a static one we can and since this is static one it is taking the table one entry all right so uh till now we got the user's file the flow got triggered we created our own Excel file with the use of compose with the format the set format and then we have listed both the users file and the Masters file close okay so now after listing it we can now do the comparison so let's save our flow first okay so let's get you can see usual entry that all right so let's do the apply it to each first foreign let's take the entry from the user's file okay so we will take users file okay match the condition so how many entries how so many entries are there in the users file we had three in our examples all those values need to be iterated so that's why apply it to it all those values one by one will it trade what we want to iterate is whether the serial number right so since see if I do Dynamic right in here you will see that I can get the serial number for the master file but if I go to the users file I will not get the serial number because the serial the user's file is not yet created right we are Dynamic we the flow will trigger when that file will be made available right the flow will get triggered so what we but we need the serial number right so what we will do is whenever the flow will get created we know that we need the item function right and the value that we need is for the serial number so we will code it okay and the serial number is equal to the serial number of what is there in the database file so you can see automatically it has put and applied to each a second applied to each Within right because the second file the database file also has multiple entries right in our case five entries are there so the first apply to each will iterate for three entries three serial numbers and the second since the master file has five entries it will iterate for five so the first entry of the users file will iterate across the five entries of the master file then this then the second entry will iterate across the five entries of the master file one by one right so the first entry five times in second entry five times the third entry five times right so 15 times it has two you're done okay let's complete so if the serial number is this then what we want to do is we want to add a row right to the Excel that we have dynamically created add a row into table location is test site three document Library document file is the file that we have created okay sure Advance option and the table that we are going for is table one okay save will give an error and that is what I'm looking for as well see the row is required okay so we need the row values what we want to fill so what we will do here is compose we have the serial number okay so the serial number let's take it from the master file then the state and then the City and that we will use in the Heroes as an output save okay so this is it our flow is completed let's delete this file and let's leave it if and need it for the template one now let's run the flow so let's go I would have to upload a file so let's download this not this one I bet let's download this one book three and delete it delete let's upload file download there it is so we have fulfilled the criteria for the trigger and this should we should have our flow running there says the notable was found with the name table one how is that possible we were getting error in this one right so I'm going to use not the ID instead the path save Ah that's that's the wrong one not the create file but rather when the file is created because that's the user file we will use the full path okay save and seeing we will do for this one as well this one has the full path and the name so that should be fine let's run since we have already in previous entry which failed let's run it now so it was giving the ID 7 but it needed the file name right like Master file dot XLS okay so we have our flow running and here is the output so the out of the five the three entries matching were these three entries the starting three entries right so if you see the apply twitch took about 12 seconds and as I said uh from the first file uses file the three entry the first entry right is getting matched across one two three four right the fourth will be as false where's the first three will be I'm sorry only the first entry of this first will be matching and the rest it would iterate one by one through all and it will be false then the second entry of the users file will iterate across all the five entries again the second entry will come as true it varies the rest 4 will come as false okay so if this is a lot of entries within the file 800 thousands of entries in the master file then it would have to iterate across all that and that is where the issues because then it will take a lot of time right instead of this 12 second it will be much higher so what we can do is uh alternative alternative to this and before that concat UTC now thank you for my tip time okay so till uh from here from compose create file list this this remains same but instead of apply to each right we will instead use are alternative right so let's delete or rather let's be select so we are going to use from the user's file right we will select the serial number foreign and by the way in the apply to each right so this one since we are dynamically getting it it would be items and in the bracket you can go and check if both becomes same applied to each two and apply to each two just change one to apply to each right so that and its items within s okay coming back select filter array okay so we will use now the second value from the master one okay and from the master we will use the output of the previous one select and this will have contains serial number okay so this let's save and let me run this all right I changed something here foreign okay so I'm naming it till the year Monday and then the art until the minute right because I'll be running test and similar name files can give error okay so now see these three steps see the difference zero seconds zero seconds let's see I'll show you the output as well right 13 second and it generated your file as well but see here so first we get only the serial numbers from the user's file okay users file had the name as well but we got only the serial number from the select right and in the filter what we are doing is that serial number with respect to the serial number in the master file we are matching and wherever the match is there it should give us so the master file since we are doing in the master the master file has five entries so let's check so this is the first entry one two look at this one zero three four six the number you can look at the state in the city as well right then Karnataka one three four seven then Tamil Nadu one three four eight and the last West Bengal Kolkata one three four nine so five entries out of this five entries what it will do it will filter this array and give us the output with everything with everything but three entries which is matching with those three serial numbers so one three four five really one three four six up and one three four seven and nautical background right so it has filtered us right five entries from the five entries based on what the serial numbers were it has filtered and given us the results right so the result is seen from this as well and the time frame we can see eight zero seconds and if there are a lot of entries within the file that it has to iterate through it would be always better for us to use the select and the filter all right let's just check see this is a better file format name and the three entries okay so this is getting generated still through the apply twitch because I have not use after filter array for us to generate so there are still more steps to be covered here okay before that okay so what next we are going to do so we are going to update this so in the filter you saw it how the output was you were having all the details now we are going to add a row to the table in our desired file but for us to do this we would have to use one more step in between which is past a Json step okay it will take the value from the filter array the body content and the schema right we will generate it from the sample so how we will do it you will insert here the output of your filter array copy this and paste it here right and then click on OK and we'll generate the format for you right serial number State CDC it has generated the format so once we do the r Json so what is the benefit of doing the partition is that now we will be able to extract those values this is it will show up in a dynamic content okay now all we have to do is we have to do and row into a table this will also go in to apply to each it should come up on its own so the file that we created it is Right add a row into table thing I'll use file name idworks okay table one custom value there it is okay so let me do this apply to each so we had three results that were coming in we would iterate those three results yeah partition body apply to each course and I will go into it and then we have to come pause within the compose right we have the serial number state and City okay and if you see see in the power station we are having this serial number the Stitch and the city for us to pick okay there is a comma that will come after this but not after the last entry and there is the compose let's bring it here and let's bring that compose in here okay save let me delete all the files all right so this applied to each right in here is through our filter array right select filter array and parsing then we are inputting that adding a row and this is the comparison is done through apply to each within applied to each right and then the if condition okay so this is applied to each within apply to each and then the if condition that is how the comparison and the filtering is done and then it is adding into the row right whereas in this one we have the filter array select and then filter array parse it and then put it into the row if I have to test it even though it's going to create only one single file right but you can easily do the comparison when it shows how much seconds the each step are going to take okay so as we can see here this is zero second zero second zero second and then say six second that has been filled whereas this is going on after 10 seconds experience almost a double the time that this one took and the filtering was way more cartridges in putting it you can imagine if you have the set of data is even more bigger than the apply to each within the applied to each and the if condition would take even more time has compared to the filter and the select so filter and select is obviously a better method here to be applied since the number of calls also would be less as well as the time that the flow is going to take right even though we have a single file that will have double entries one from the applied to each one the first one is through result select method and the last three is to the apply to each okay this is what I wanted to show mainly in this the comparison between select filter array and apply to each okay following this in the same one I'm going to show you how to use a file system but that is going to be another video okay thank you have a good day

Channel: Mahfuz

Views: 2,631

Keywords:

Id: -odX-LeBJ5w

Length: 42min 46sec (2566 seconds)

Published: Fri May 26 2023