Importing excel data by Filename partial match of the first 13 char, then import the latest modified

[Deevo_]

Hi All,

• I have a report that uses an excel file as a datasource. 
• The excel file lives in a folder with many other excel files with different filenames.
• I have to manually copy and paste this file in another folder and point my report to that folder to import the "latest modified" excel file.

Question:

• I am trying to streamline things abit and wanted to know if there is anyway to search all filenames within a folder and pick all the excel file with the same first 13 characters in the filename and then only use the "latest modified" file as my datasource?
• Example:
  • Step 1: search all excel filenames starting with 'Timesheet Raw%'
  • Step 2: Sort by Date Modified Descending
  • Step 3: Only keep the first file from the search results
  • Step 4: Then add all my transformations

Thanks!

[mlsx4 · Accepted solution]

I think you can add the steps in the Power Query editor without any problem:

1. Connect to folder as datasource and go to the first step in the editor. You will get something like this:
2. Filter rows by extension (xlsx o xls maybe)
3. Filter name of file in the column -> Filter by text -> Starts whit...
4. Once filtered, order by DESC date
5. Keep the first row.

[Deevo_]

Thanks to both @Nithinr and @mlsx4 

This worked perfectly!

Much appreciated!

[Nithinr · Accepted solution]

let
Source = Folder.Files("\\Filepath"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Name], "Timesheet Raw")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Date modified", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1)
in
#"Kept First Rows"

After this you need to expand content to get that excel data

[mlsx4 · Accepted solution]

I think you can add the steps in the Power Query editor without any problem:

1. Connect to folder as datasource and go to the first step in the editor. You will get something like this:
2. Filter rows by extension (xlsx o xls maybe)
3. Filter name of file in the column -> Filter by text -> Starts whit...
4. Once filtered, order by DESC date
5. Keep the first row.