Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Loading latest file from multiple sub folders

Hi, I am building a management weekly dashboard connecting to ADLS. I have to keep 12 weeks of data in perspective at any given time. Each file has a Month to date data. The data set will look like ...
Need Help
  • edhans's avatar
    edhans
    6 years ago
    1. Filter your files so only releveant data is showing. For example, only XLSX file (or CSV or whatever they are), and only the folder structures you want - root\presentation for example. Call this "File LIst"
    2. Create a reference to that query. Call this "Latest File Name"
    3. Use the Group By function in Power query to group by folder and show the maxium file date.
    4. Create a reference to the File LIst query and call it "Final Files"
    5. Merge Final Files with "Latest File Name" joining the folder name and the file date. Use Inner Join
    6. Now you should only have a list of the last file in each of the relevant folders.
    7. Press Combine.
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

We can use this query in power query editor after you connect to the root\presentation folter using Folder Connector:

 

    #"Filtered Rows" = Table.SelectRows(Source, each [Name] = ("file_wk" & Number.ToText(let path = [Folder Path], t = Table.SelectRows(Source,each [Folder Path]=path), t2 = Table.AddColumn(t,"WeekNumber",each Number.FromText(Text.Replace(Text.Replace([Name],"file_wk",""),[Extension],""))) ,t3 = Table.SelectColumns(t2,{"WeekNumber"}), l = Record.FieldValues(Table.Max(t3,"WeekNumber")){0} in l) & [Extension]))

 

 

All the query are following:

 

let
    Source = Folder.Files("D:\root\presentation"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Name] = ("file_wk" & Number.ToText(let path = [Folder Path], t = Table.SelectRows(Source,each [Folder Path]=path), t2 = Table.AddColumn(t,"WeekNumber",each Number.FromText(Text.Replace(Text.Replace([Name],"file_wk",""),[Extension],""))) ,t3 = Table.SelectColumns(t2,{"WeekNumber"}), l = Record.FieldValues(Table.Max(t3,"WeekNumber")){0} in l) & [Extension]))
in
    #"Filtered Rows"

 

 


Best regards,