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