 Hello everyone and welcome back to another video. So in this video We're going to be looking at again power query And if you watched our previous video then the screen we have at the moment will look very similar in that video We looked at a solution for being able to combine every single one of the files We're going to give in folder this time We're going to look at the scenario where you only want to bring the latest file through to your report So in our scenario, you can see we've got three files here and each file is dated at the end of a calendar month So at the moment we've got the 30th of November the 31st of December and then also the 31st of January And if you're not familiar with this file naming convention Basically, you can just see they've all got the same name of snapshot and at the end You can see that you've got the calendar in a number format So 2021 is the year 11 being the month and 30 being the day for the first one and so on and so forth This just gives them the ability to obviously order organize our files In the in the date format that they appeared basically So what we'll do is we'll jump into Excel and go and get straight to it So the first thing we're going to do is go to our data tab and from here We're going to go to the get data option and simply just launch power query editor From here, this bit will seem very similar again Like I say to the last video but bear with and this will save us a good recap If you can't remember how we got the file contents All we're going to do is right-click go to new query other sources and then enter a blank query And I'm simply going to type into here equals folder dot files Because we want to be getting this information from a folder So again for clarity, I'm just getting stuff from a folder that appears on one drive Obviously, if you're pulling from other sources like share point or any other particular scenario Obviously, this particular step is going to be different as you connect to your source But the following steps in terms of how we sort the data Are going to be exactly the same across platform applications. So they say I'm just going to cheat and copy my folder path across because I've got on the screen to the side here and Upon doing this, we'll get rid of the error message you now see on the screen. So let's place that into there Hit enter and you can now see our three files and Perfect they in the order that we just looked at them So what we want to do is the first part of this is we want to understand what or fine Should I say the latest file in this group of files? So in order to do that and this is where our naming convention really comes into play Because we've named all of these using this similar number format. It gives us this Ability to obviously organize our files in the calendar order they appear So obviously by creating this number from year month and day it gives us a Reference should we say that's obviously continually going to grow so the latest file is going to be the one with the Greatest or the largest number is one way to put it So in order to do that we can first bring our latest file to the top of the list So to do that all I'm going to do is go to the name column Select the drop-down and then I'm going to go to sort Descending so the second option of the top of it and hit that and you can see it's just now flip the order in that We had it originally was ascending by default and now we've put it into descending as you can see at the top here So that's brought it to the top. So, you know what to do it The next thing we want to do is we want to basically disregard and get rid of all those other files So in order to do that, I'm going to use this great function called keep rows So all we need to do if we're in the home tab is you'll see there's this option for keep rows I'm just going to do the drop-down and you can see we've got a number of options available to us But what I'm going to do in this scenario is just go to keep top rows And for us we only want the the single latest file So we're going to go keep one row and when we refer to rows, we're not talking about the content within the file We're simply talking about the rows that you see here. So the rows of information within our folder path So if we do number of rows one and hit okay You can see that everything else has been filtered out so that we can only see our latest file that we're obviously wanting to use So the next part then is to absorb the information in that file and to do so again I'm going to follow this the same step we used in a previous video where we're going to combine all Of the available files you see or all of the visual files you see at the moment Obviously, it's only going to be one file But it's just a way of achieving the import of this without having to Specifically define this name as what we want obviously well not obviously maybe but we can't we could just import this file now But what that will do and probably to explain my waffle just then is it will capture the name and the exact folder path Or file path should I say for this file that will work in this scenario? But as we go through our example and we look at entering new files to this obviously it's going to continually pick up this file We don't want that we want the solution to be completely dynamic. So as new files come in It's always going to pick up the latest one So the next thing I'm going to do is I'm going to right click on our applied steps And I'm going to go into insert step after you can see it's created this custom step for us If I just navigate between the equals and before the hashtag You could delete that keep first rose, but it just helps rather than having to try and type that again Again for clarity keep first rose is all it's doing is referring to our previous step, which is keep first rose So it's going to sort of we're now going to tell or create a function Which is going to apply logic to that the data set at that point What we're going to do here is going to type in binary and as soon as we do full stop You'll see we've got the options available to us I just want to go down to the combine option and I'm going to do tab to select that I'll do an open curve bracket and then go all the way to the end of this keep first rose step and In square brackets I'm just going to enter the word content because we want to get the content all content available Which is obviously the files content One the last curve bracket and hit enter and you can see it's now pulled that data through for us So if we want to do we can now go Close and load and obviously that information will be loaded to the file for us But if you've seen our videos before you're more than familiar on how to do that And you can of course check those videos out if you want to understand how to do the close and load and add it To the file But for us we now just want to make sure that this solution is robust for new files being added So in order to test that what we're going to do is if I just open up our folder view We've got the moment here So we can still see that we have the three files available to us in the snapshots folder If I was just to go and get a copy of a new file So we're going to paste in here one from February So we can now see we've got yet twenty twenty two February being the second month and then the 28th to the last third of month So we've now added a new file to this folder So what will happen is if we now come over to go back into power query in the background here I'm just going to click this refresh button at the top here because it will then rerun this query And what it should do is pick up the new file In our folder obviously and then go for those steps of putting it to the top Excluding the others and then only presenting the data within that file. So if we now push refresh preview If I clicked it you can see that the content has now been updated And we've been able to validate that because this field of file name is actually contained within the file Use these values if you want to be specific. You might have seen that they didn't change It's simply because I was quite lazy and just took a copy of the This January file and just changed the date to give us a reference point So we can see that that has worked if we want to reverse that and let's say we now go and remove this February file Like so so you can see there is only those three files now there and once again refresh You can see how obviously now the January file has regained that position as the latest file So this is a great solution if Unlike previous videos, we don't want to pull all information in we only want to pull the latest Available file into our report. So I hope you enjoyed that video if you did Please don't forget to give the video a like it's not only greatly appreciated by me But it does help the all-important the YouTube algorithm And if this is your first time checking out the channel or you've watched our videos before Please can you consider subscribing to the channel and also hitting that Bell notification button that way you'll be notified of all Of our videos as they come out in the future. So once again, thank you very much for watching and we'll see you in the next video You