 Hello everyone and welcome back to another video. So in today's video, we're still on the theme of power query But we're going to address the issue where we have a number of files that we wish to combine To create one data set that we're able to use in our Excel file and then be able to obviously visualize from there on The great benefit of this if you're watching this video and you're either requiring this in Excel or maybe something like Power BI This this exact solution is transferable across both So first thing you can see on the screen at the moment is we've got our file explorer open And we can see that within this folder of snapshots. We have three files So in this scenario, maybe the end of every month this snapshot is taken and it's obviously capturing data For us, we'll see the data as soon as we pull it into power query But it's a real simple 10 row 10 or 12 rows per file Containing the date at which it was the snapshot was captured and also a value per each individual So we'll treat this as maybe sales data. So we'll without further ado, we'll just jump straight into it so any of you familiar with power bi may be aware of a Not to say a quick aware doing this But I guess it is quicker but maybe an out-of-the-box way of doing this in which you can go to get data Go to From file and then go down to maybe from folder I am not going to go for that solution just because when power bi does this for you It creates a number of unnecessary steps So we're going to be using obviously a lot of manual Well, not a lot of but we're going to do a couple of manual formulas But you'll just see how straightforward and simple it is to achieve So if you've done this process before I'm showing on the screen Yeah, you have a something to compare to if you haven't done that process before By all means have a play around with it But I think the solution that I'm going to show makes it a lot more streamlined and simple to follow as well So what we're going to do is we're going to select to go data and we're going to go straight to launch power query editor And once power queries load itself obviously at the moment We've got no data in here So it looks quite bland what I'm going to do is right click in the queries paying over the side here Go to new query and then I'm going to go down to other sources and go blank query So in here, we are now going to find or locate our file location or folder location So I say more specifically so very easy one to remember all you do is go equal folder and Then you can see the next one is folder dot files and I'm going to open my brackets and just for Laziness I've got the link I required just the side over here on another screen You can see at the moment It's obviously given an error because nothing has been provided But if I just now paste that in there and obviously of course you can then change this This file path as as required Once I've entered that I'm going to hit enter and you can see we're now seeing our file and Here we can see you've got that file again that folder open again So we can see our three files in here and you can see they map exactly or not mapping see them exactly same in here So this also gives you some added flexibility should you need to incorporate such information into your reports We'll leave this for now, but obviously that's another one that you can play around with So the next thing we need to do is how can we combine these three files? In this scenario into one data set and it's important to mention here as well that the solution We're now going to enter when more files are added to this folder So maybe at the end of this month or next month there will now be four files rather than three This solution will include that additional file to make sure that our data set will continue to grow as time goes on So what we need to do over here is I go to our applied steps I'm going to right-click and go in set step after and you can see at the moment the step is just defaulted So we've got our source at the moment Obviously what's going to go get the the files in that folder and then custom is currently just referring to exactly the same step What I'm going to do though is if you just remove source for the time being I'm going to enter the formula binary Dot combine. Oh and it's gone for me. So just go type in the binary dot combine and Sometimes power query will do this. You can see it's doubly entered the binary So I'm just going to make sure that I delete that one off before it gets confused later on Open brackets and simply all we need to do now is refer to the previous step. So for us, it's called source But if you were I don't know for whatever reason your step your first step or step referring to was called something else Then obviously it would be referring to that step instead So I'll just type source and you can see it's popped up with a suggestion So I'm just going to hit tab so it's selected and then the last thing I need to do is do a square bracket and type the word Content and you can see it's now put a closing square bracket and also a closing curve bracket for the function of combine and In most simplest terms what this is doing is just saying combine all the sources Content is one way you could put it. They might not be the most technical answer But it simply put that's what it's doing if we now hit enter You can see how it's done exactly as required. It's gone to all of those files and combined all the information together so you can see we've got obviously the Headers for each file of obviously being included in here and likewise for each file You can see we've got the November snapshot the December snapshot and then lastly the most recent January snapshot and how we've got Like say the first two columns or first three columns of ID name and region are all gonna be the same and then each month It's gonna obviously have a unique file date, but also unique figures here as well So we just need to do a couple of steps to tidy up our data So the first thing I'm going to do is promote the first row of the data set to headers So simply under the home table got selected here halfway across you can see first use first row as headers So if you select that you can see that in scenario has now been fixed The next thing we want want it Well, we will want to address is we don't want these column headings appearing in every you know throughout our data set We literally just want See one set headings and then obviously our dates so all I'm going to do in the file date But again, you could apply this anywhere suitable is I'm simply going to go into the drop-down and I'm just going to deselect file date and Go okay, and you can see that's now created this filtered rows step for me And simply if we now look at it it says obviously change type one, which is our previous step Each file day is not equal to file day So any of you not familiar if we wanted it to be equal to file day you would see this symbol whereas the less than greater than that symbols combined meet Equate to does not equal so we can see that's worked for us as well And yet we then have our data set that we require So two last things I'll just observe as having said that is this file date at the moment is being treated as text I don't want that I want it to be recognized as a date So I'm just going to select the ABC we currently have in the top left for that field And I'm just going to navigate down and select date and you can see it's now converted that to a date for me and likewise for sales, so we're just going to just quickly try and see if we can convert this to a Whole number you might want to do decimal working with Sales amounts, but I'm just going to go for a whole number at this time and Yep, you can see it is converted that for us as well. So let's just check out other ones Yeah, I'm happy employee idea is text name is text region is text sales is a whole number but alternatively obviously like I said You could make that a decimal if you did want decimal places in your values and our last file date We can see has been stored as a date as well So and that yeah, I'm probably just worth noticing You can see the steps have been applied as required and we can now see the actual formula should we require that? So obviously rather than do this manual you could if you could remember it you could obviously type this in Straightaway rather than having to do it manually Last thing to do is going to change this to change the rename. So let's call this something in the lines of Snapshot data through all lack of Better terms so you can see it's now see renamed in the query over here so last thing I'm going to do is go closing load and Obviously it will now refresh and pull through that data for us and there we go We have now got our data All combined into one table for us here called snapshot data It's we've got sheet one here as well. I'm not going to use that So I just deleted that off So the last thing obviously the benefit of doing this is we want about to now present this data So we could simply go into here and let's say let's put in a pivot table. So let's go insert pivot table From table or range and we can select it from here snapshot data So I might done that a bit quick if you navigate to the top left of your table You can see your black arrow Change so it goes into a diagonal as soon as it does that if you select it will identify that you've selected The table alternatively if you knew the table name, you can just type that in here and it will do the same for you And we can just validate. So where is it going to put our pivot table? Okay, location snapshot data, which is our tab name and it's going to go into I1 So yeah, obviously the selection we made here. Let's just do okay And now what we can do is obviously the benefits of having this all into one data is we can now Separate our file dates. So let's just I don't want quarters and years. I just want to have the date And let's say we want to do this by region and then we'll bring in our sales amount And we can see we've now got it there So we can now see all the benefits now bringing this all into one file is we've now got our sales by month and Obviously the total that each region has done an alternatively you could obviously demonstrate this by a person's name as well And as I mentioned halfway through there The real benefit of obviously doing this solution is as new files or snapshots are added to that folder direct Folder destination then obviously every time we refresh our data set And simply all we need to do for that is we right click our data set or our table and we can go down to refresh It will pull through the latest data. So we're able to report on it as well Again, the real benefit of this is it just keeps all those Separate snapshots or those snapshots snapshots separate. This is what I'm trying to say Rather than like, you know having to maintain a manually in one master file So it's a real benefit of using that way. So I hope you enjoyed that video And it gave you a solution if you're looking for this problem Or it's giving you some ideas on how you could improve one of your existing reports If you liked the video, uh, please do give the video a like It's not only greatly appreciated by me, but it does help that all important youtube algorithm And if it's your first time checking out the channel or you've watched our videos before Could I please ask you to consider subscribing to the channel? Obviously it will not only help me But again, it will notify you once you hit that bell notification button of all of our future videos as and when they come out So once again, thank you very much for watching and we'll see you in the next video