Get data from the latest file in a folder with Power Query | Excel Off The Grid

in this video we're looking at how we can connect power query to only the latest file in the folder that means we can keep dropping in files into the folder but Power query only connects to the latest file so let's head over into Excel and see how we can do it here we've got three files in a folder and we want to connect to that folder using power query so to do that we'll go to data get data from file now even though we want the latest file we need to use from folder so I'll select that we then need to navigate to the location where our files are saved all my files are saved in my report files folder and then I'll click open power query is now connecting to our folder you can see here that we have those three files I'm going to click transform data in the preview window we have four fields that we can use to determine which is the latest file that we want to connect to first of all we have the date created now don't be fooled this isn't the date that the original file was created but the date that the file was created in that file location so if we copy a file and move it to another folder the date in that folder will be the date that we copied that file the next column is date modified now don't be fooled with this one either it isn't necessarily the last date modified it's actually the date that the file was last saved so if somebody opens a file and saves it without making any changes that will be the date modified last of all we have date access and that's the date and time that somebody last accessed the file now because of these quirks around date created and date modified it means it's possible to have a file that was modified before it was created therefore it depends on your circumstance as to whether these fields are useful we can also use the file name so here in this example I've used a file name that contains a date therefore I can easily determine what my latest file is based on my file name and that's what I'm going to use in this circumstance but you can use any of these other fields if they work for you so I'm going to click on my name column then from the home ribbon I can sort in descending order that puts the file we want at the top of this table now we could click into the binary that will connect to that file however you'll see that in the formula bar it's hardcoded that file path which won't work if the next time we refresh our file name has a different name so instead we're going to write a small piece of code ourselves I'll click on the FX icon that gives me my last step name of sorted rows and all we're going to do is use sorted rows square bracket content because that's the name of my content column and I want the first row from that so in curly brackets I'll enter zero then when we click away it now drills in to that first file let's expand that and see how we can use this data let's just use those two columns so we will remove the other columns use the first row as headers perfect now let's load that into Excel so we'll go to home and then close and load right that's now loaded that file as a table in Excel let's go and add a new file into our folder going to come back and I've got a new file here on the 30th of November 2023 I'm going to cut that file and paste it into my report files so let's see if it now picks up that new file I'll go to data then click refresh all perfect that data changed it's now connected to the latest file in that folder and that's it that's how we can connect to the latest file in a folder now the easiest way is if we can rely on that date created that means that we need to restrict access to that folder we need to make sure that other people don't copy and paste files into that folder that might end up resulting in the wrong file being recognized as being the latest file file if that's a possibility then use the file name we can always control that file name to make sure it is the correct file so thanks for watching and I'll catch you next time

Channel: Excel Off The Grid

Views: 6,628

Keywords:

Id: VJAoYaWiKCs

Length: 4min 40sec (280 seconds)

Published: Wed Nov 29 2023