Select the latest copy of an Excel sheet

[HMJSomerset]

I have a Power Query that includes a lookup table from an Excel sheet.  This sheet is produced and stored in a folder named

ETL/20230210/xxx.xlsx.  The following day a new version is produced and stored in a new folder ETL/20230211/xxx.xlsx.

How can I always select the most recent file, without having to manually edit the query

[Adamboer]

You can use a dynamic file path to always select the most recent file in your Power Query. Here's how you can achieve it:

1. In the Power Query Editor, click on the "Home" tab and select "New Source" > "File" > "Folder".

2. Browse to the folder where your Excel files are stored (i.e., ETL).

3. Click on the "Combine & Edit" button to open the "Combine Files" dialog box.

4. In the "Combine Files" dialog box, select "Binary" as the file type, and uncheck the "First file defines headers" option.

5. Click on the "Edit" button to open the Power Query Editor.

6. In the Power Query Editor, click on the "Add Column" tab and select "Custom Column".

7. In the "Custom Column" dialog box, enter a name for the new column, such as "File Path".

8. In the "Custom Column" dialog box, enter the following formula:

= Text.Combine({[Folder Path], Folder.Files([Folder Path])[Name]{0}}, "/")

This formula will dynamically generate the file path for the most recent file in the folder by combining the folder path with the file name of the first file in the folder (which will be the most recent file, assuming the files are sorted by date).

1. Click on the "OK" button to add the new column to your query.

2. You can now use the "File Path" column in your query to reference the most recent file in the folder. For example, you can use the "Excel.Workbook" function to load the data from the most recent file as follows:

= Excel.Workbook(File.Contents([File Path]), null, true)

This function will load the data from the most recent file into your Power Query.

By using this method, your Power Query will always reference the most recent file in the folder without the need for manual editing.

[HMJSomerset]

I have managed to find a solution, though I don't know how elegant it is.

I first loaded a sheet into Power Query

Then I opened Advanced Editor and I created 2 variables, 

TodayFolder to return the today's date in yyyMMdd format and then Folder path always point to the current folder

TodayFolder = Date.ToText(DateTime.Date(DateTime.FixedLocalNow()),"yyyyMMdd")

TodayFolder = Date.ToText(DateTime.Date(DateTime.FixedLocalNow()),"yyyyMMdd"),
FolderPath = "https://xx.sharepoint.com/sites/xx/ETL Daily/"& #"TodayFolder" &"/xxx/",

I then replaced the yyyMMdd date instances with #"TodayFolder" and #"FolderPath"

[HMJSomerset]

Sorry, I should have said that the folders are in a SharePoint document library.  The solution you have given returns the latest file in that library, not the latest version of the file that I need.  

Is there anything else that I can do?

I have managed to get a variable to return the latest folder name, 

TodayFolder = Date.ToText(DateTime.Date(DateTime.FixedLocalNow()),"yyyyMMdd")

Is it possible to insert the variable in place of the date?

[jennratten]

If you are looking for the latest version of a file, then you can use the the Date modified column instead of or in addition to the Date created.

[jennratten]

Instead of connecting to the file directly, you can connect to the parent folder (ETL) and filter to get the file with the latest value in the Date created column.  Then you can further filter the folder contents if needed, and then finally select the Binary in the Content column to get the file's contents.

let
    Source = Folder.Files("C:\ETL\"),
    #"Filtered Rows" = Table.SelectRows(Source, let latest = List.Max(Source[Date created]) in each [Date created] = latest)
in
    #"Filtered Rows"