To pick latest file excel in a folder but it can be multiple file

[conniedevina]

Hi All,

I have an issue that I have these year folder

2021, and 2022 folder.. I want to pick up the latest file from each folder. but the file format might be like this

1a. - for January

2a. - for February and so on..

let's say this the structure of the folder and files

2021

1a. ABC

2a. ABC

2022

1a. ABC

2a. ABC

2a. BCD

I would like my power bi pickup data 

2021 -> 2a. ABC

2022 -> 2a. ABC and 2a. BCD

this number will be added based on the month, is this possible to be done? as I check the forum they mostly the answer about latest 1 file.

Would appreciate any help!

[MFelix · Accepted solution]

Hi @conniedevina ,

For this try the following:

• Add custom column with the beginning of the file name (text before delimiter):

• Add another one with text After Delimiter

• In both cases delimiter mus be the space
• Do a group by folder and Text After Delimiter column using the MAX of the Text before delimiter, and all the rows:

• Expand all the columns on the Value column except for the folder path and text after delimiter
• 
• Add the following colum to you table:

[Name] = [FileStart] & " " &[Text After Delimiter]

• Filter out all the false values

Complete code below:

let
    Source = Folder.Files("C:\Test"),
    #"Inserted Text Before Delimiter" = Table.AddColumn(Source, "Text Before Delimiter", each Text.BeforeDelimiter([Name], " "), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([Name], " "), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Text After Delimiter", {"Folder Path", "Text After Delimiter"}, {{"FileStart", each List.Max([Text Before Delimiter]), type text}, {"Value", each _, type table [Content=binary, Name=text, Extension=text, Date accessed=nullable datetime, Date modified=nullable datetime, Date created=nullable datetime, Attributes=nullable record, Folder Path=text, Text Before Delimiter=text, Text After Delimiter=text]}}),
    #"Expanded Value" = Table.ExpandTableColumn(#"Grouped Rows", "Value", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Text Before Delimiter"}, {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Text Before Delimiter"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Value", "Custom", each [Name] = [FileStart] & " " &[Text After Delimiter]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true))
in
    #"Filtered Rows"