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 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.

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.

6 Replies

  • edhans's avatar
    edhans
    Community Champion
    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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

  • 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,

     

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,