Loading latest file from multiple sub folders

[Anonymous]

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 this:

root\presentation\September folder

    file_wk4

    file_wk3

    file_wk2

    file_wk1

root\presentation\October folder

    file_wk4

    file_wk3

    file_wk2

    file_wk1

root\presentation\November folder

    file_wk3

    file_wk2

    file_wk1

I have a need to read the latest data from each of these folders. I am able to pick up the latest file from One folder.. However, I am not sure how I can pick the latest along with keeping the last file from the historical folder.

In this case:

I would like to keep the data from: 

• September - file_wk4
• October - file_wk4
• November - file_wk3

Any help is really appreciated. Thanks

vk.

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

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,

[edhans · Accepted solution]

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.

[Anonymous]

Hi - Thank you for your step by step suggestion. I am able to go up to Step #6. It shows me the correct list of files. However, I am not able to combine them. The "Combine" button is grayed out.

I tried merging from Latest File Name as well as Final List. Any suggestions.

Thanks

[Anonymous]

Never mind - I figured out how to combine them. I went and clicked on the column Binary and it triggerd a combine.

Thanks for your help.

Sundar

[edhans]

Great! Glad you got it working.