 Hello everyone and welcome back to another video. So today's video is actually probably one of the most requested videos I've ever received and it's back on power automate and it's Demonstrating how we can use the tool to combine multiple files from in the same folder So the scenario look at today is we've got some old sales data a game mentioned sales data This is all complete random data. So there's no Sense of data being obviously viewed here at all just in case that comes to mind So we can see if we go into snapshots We've got three files and do ignore the historical dating on them and each file contains some Sales data for that actual date So if I was to go into this one for the 31st of Jan You'll see we've got five columns of data and what 13 rows or 12 rows if you exclude the headers And what I want to do is for each of these files in here We want to combine them into one master file So we've only got three files here But regardless of if you've got three files or a hundred files this process will work based on the files content So whatever's in that folder. It is all going to get combined into this process So what we'll do also one thing to mention. I've also got a templates file here called headers So just some context before we jump into that all this simply is is what it says a template file for headers It's just an easy way for us to pick up this file and to use the headers in here rather than having to paste them in a step So template files are great So it's a trick to save time when it comes to using the headers But just for context as when we look at that later on, you know what that is So in power automate will jump straight into it So the first thing we need to do is obviously identify the files within the folder that we wish to combine So simply what we're going to do is go into our folder option down in the left-hand side here And we're going to go down to get files in folder and we'll pull that across into our main pane here So we just now need to navigate to that folder So for me, it's going to be if I can remember where it is It's going to be in here and it's going to be in snapshots So we're just selecting the folder as a whole and as you might have seen in previous videos There's many options we can apply to this we can even do some filtering to the files if it's applicable But for us today, we don't need that. So we'll just simply click on to save and Then next before we get into looking at each of these files We're then going to go into launch our template file So we're going to excel and launch excel drag that into the middle there and We don't want to open a blank document because we want to use our template file We just looked at which contains our headers. So again, just to remember where I have to source this from So I think it's into these folders at templates and then headers and we'll go okay That looks good. Yes, we'll leave it as visible It's not a problem and this is where obviously I don't know if I touched on this before if you scroll down You're actually able to rename your variable if more useful. So Excel instance Let's put this Excel instance Output just so as we look at this as we build we know that output is going to be our final combined file So let's save that So I believe we now have the two key elements we need to start Going through and getting this data So the first thing will not the first thing because obviously it's now the third thing But we want to now loop Through each of these files in this folder or the files that we've got in this very first step here So what we need to do there is simply search for each I'm very lazy. So I was like to use the search box there and once it's worked out what it's doing Sometimes take a bit of time yet. We can see within loops. We've got this option of for each So this is logic that we want to apply to each of the files that are in step number one within this folder So the value that we need to iterate here is simply going to be current item. So if we just go into our our Variables on the side here, sorry We can select list of files select that and you can see it's going to store it as current item So basically what it's going to iterate through is the list of files. We can see at the top here This being the variable that's been Received from going into the folder. So if we do save on here, we've now got our loop set up and ready to go So what we'll now need to do and again, sorry for jumping over jumping around But hopefully this will start to make sense as we step through this is we're going to go back and do an Excel process And what we need to do is we need to understand which is the next available row within our template folder So if you're going to imagine what's happened here is we've launched our Excel file containing our headers So we know there's data in row number one, but we now need Power Automate to identify that the next free row or next available row is going to be row number two And that's there where we want to now paste our data so we need to go into the advanced section here of the Excel options and We write the very bottom. You can see there's get first three row on column So we're going to click and drag but this time make sure we drag it into our for each loop So Excel instance, so which Excel files are going to be looking at? Well, we've only got one open at the moment and we can see it's Excel instance output at the top here And if we to do this drop down you can see there is only the one option had we got multiple Excel files open You would see obviously a lot longer list there So for us, we're only interested in column A All columns hopefully are going to be the same but ultimately column A Serves as the good tester for us to know which has got the next available free row Okay, so now that we identify which is the next free row We can start loop or actually opening each of the files within our for each loop So what we'll do once again will launch Excel make sure it brings into the for each Again, we don't want a blank document What we actually want is to open the following document and where it says document path here All we need to do is in the variable here select current item So current item being the current item we're on Within our loop of all those files. So select current item do select Make instance visible now not interested in making invisible, but you can if you wish and we shall go save So you can see that's ready for us So we've now opened that first file the next thing you need to do now is obviously read the data within that file So if we go into again our List of Excel options, we should have an option here from read from Excel worksheet Yeah, so you can see just down here read from Excel worksheet. We'll drag that into there Excel instance, so now we're gonna have multiple options available So we've got our output file, which is one here, but we need this Excel instance So this is the current one that we're working with Obviously having loop through the files. So what do we want to retrieve the value of a single cell? No We want all available values from within the worksheet. Obviously you could define a specific range of cells here But what we're going to do is keep it the most basic option variable and just take all available values from the worksheet We're also quite confident that all the fat We we are confident that all the files are gonna have that exact same template because there's another process that is Building those files in the first place, but again, we'll stick with a simple option what we've got here So we're now reading all of that content Then what once we've read this data, obviously we now need to paste it into our output file So in order to do that, we will go down and find a right to Excel There get just slightly big There we go right to Excel So this is where we're now obviously going to be using the information we gathered up here the get first free row on the column So the Excel instance we want is the Excel instance output The value to write is going to be our Excel data. So where's it gone data table? So this second one here. So this is the Excel data that we've got when we read from this Excel worksheet So we can go select on there Right mode on that specific cell So we want to go into yet on specific cell and that specific cell for us is going to be column a and The row is going to be if we go into here once a game the first free row on the column So if you select okay, so we can see what's happening here is we're now looking at our output file We want to paste into that file Excel data what was just captured from this the last file opened and we want to paste it on to a Specified cell and that specified cell was within column a and it's going to be the first three The first free row in the column So what we've captured here and then probably what you can start to see is the reason for this get first free row Being at the start of the each loop is for every time it goes into a new file And obviously as this file the mark the output files gets bigger. Obviously that free row is going to become Lower and lower in the list as more data is added to it. So if we click save We've got that element done. So now all we need to do is tie it off So the first thing I'm going to do is close obviously the current file. So Excel instance So we want to do Excel instance here because that's what we want to close we're not bothered in saving the workbook and Then lastly what we want to do this will now obviously loop through and do every single file within our specified folder But once it's all done the last thing to do will be obviously to close our template file We've got here. So what we're simply going to do is we'll go into close Excel And we'll bring this outside of the for loop because we only wanted to close once all the iterations are done So it's a Excel instance is going to be the output Before closing. Yes, we do want to save so go save document as Default extension so we leave the extension as it is but for us the file path is simply going to be Navigable will navigate to where it's going to be so documents Excel I've got create a new folder called merge And I'm simply going to call this combined but again You can obviously call it something a bit more creative and then we'll select okay and finally save So this is the extensive the extensive list the whole ten steps We need to obviously build this combined file. So what should we should do as always is less minimize this and We'll bring up our file explorer for our not our template one, but we'll go into our Destination file, so let's go into here. There we go. We've got it ready So what she hopefully see when we click run on here is it'll go through and loop through here I think it was three files and ultimately we will see a combined file being created in this merge file here So we're out for a delay. Let's hit play. I say play. It's actually called run Just to be really specific Cool. So you can see it's open in the file and then you'll see it start to continuously loop through all of these steps here and Obviously, we can see it looping around on the screen and shouldn't take too long because not many files And yes, we can see it went down to that last step and finished and we've now got a new file created over here Cool combined, so if you just double-click on to that and they see how the content is looking Yeah, we can see that we've not Now got all of our data is in that file However, we can see that our column headers are being kept in here multiple times And that's because I've made a mistake and not changed one piece of information So what we'll do is we'll go and delete that combined file as if it never existed So what we need to do now is just go back into our flow and specifically step number six So read from Excel worksheet So we'll just double-click that and within the advanced section You can see we've got first line of range contains column names So we'll just tick that just so it knows that we're not interested in that information and click save And once I save yep, we're all good to go So one more time will just click run and hopefully this time it will now work as intended See you got his head. It's got the header file now open It's going to continuously loop through and open all those other files and you can see the variables obviously updating as we go through Great. So we're now got to the end just waiting for it for it to finish Yeah, and then we can see we've got our combined file. So let's now open this one and There we go, we can see the data is looking a lot better and how we want it So if we just expand these columns, we can now see all of our data has been captured We've got the third from November 31st to 12th and the 31st of January So we know all three data sets have been captured and of course our column headers are only appearing once Rather than multiple times like we saw earlier So one last thing I just noticed as well is our flow seem to keep looping at the end there So all I'm going to add in there as well is if I search in to stop And again, this is probably good Good to know for good practice once obviously it finds the options. Yeah, we've got flow controls So I'm just literally going to go down and bring this stop flow at the bottom here and flow Successfully just so it knows that that's obviously the last step is number 10 And then it can exit the flow just so there's any confusion. It's not going to continue running So there you go I hope you enjoyed that video and it gave you an answer to your question If you're one of the many people who contacted me asking for such a video if it did Please don't forget to give the video a like is it's not only greatly appreciated by me But it does help that all-important YouTube algorithm if you haven't yet subscribed to the channel Please can I ask you to hit that subscribe button and also hit that bell notification button that way you'll be notified as More of our videos come out in the future So thank you very much again for watching and we'll see you in the next video