Livio/ May 28, 2018/ Excel, Power Query/ 0 comments

List all Files from a folder and all subfolders with Power Query

A common task some people have is listing all files within a folder and its subfolders and then sort them by either file name, or date modified, or date created. Power Query makes this task super easy to perform.

Download the file

In my previous post I showed how to do this using the Shell function with VBA.

The first step is to set up a simple table with three rows listing the Folder Path, the field to sort On and the Order with which to sort. The order field can have a value of either 0 or 1. If you input one the sort will be performed in descending order and if you input 0 the sort will be performed in ascending order. The field SortOn can contain one of the following values: Name, Folder Path, Extension, Date accessed, Date modified, Date created. Since the language behind Power Query is case sensitive you need to make sure you type them exactly as I have indicated.

Tip: create a Drop Down list within your table to make sure you do not mistype anything. 

The table, which you have to name Params, will look like this:

List Files with Power Query

Create a custom function to retrieve the Values

Go to Power Query, create a Blank Query, open the Advanced Editor and type the below code:

(paramToSearch)=>
let
// find the position of the paramToSearch within the Name column of the Params Table
Source = Excel.CurrentWorkbook(){[Name=”Params”]}[Content],

NamePosition = List.PositionOf(
                                            Source[Name],
                                            paramToSearch,
                                           Occurrence.First),

// return the corresponding value in the Value column
ReturnVal = Source[Value]{NamePosition}
in
ReturnVal

Then make sure you call the function fnReturnParam:

List files with Power Query

Now you are nearly done. We just need to create the main Query which will return all the files. So create another blank query and add the below code:

let
   // return the settings
   FILE_PATH = fnReturnParam(“FilePath”),
   SORT_ON = fnReturnParam(“SortOn”),
   SORT_ORDER = fnReturnParam(“Order”),

   Source = Table.Sort(
                                Table.SelectColumns(
                                                              Folder.Files(FILE_PATH),
      {“Folder Path”, “Name”, “Extension”, “Date created”, “Date modified”, “Date accessed”}
                                                             ),
                           {SORT_ON, SORT_ORDER})
in
Source

 

Now you just need to go to the Home tab and chose Load To and taadaa! you have a dynamic and ready to use query which will list the files by the order chosen!

 

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*