Power Query: Fold(er) Away
27 February 2019
Welcome to our Power Query blog. This week, I look at some of the M functionality for dealing with folders.
Folder.Contents()
This returns a table containing the properties and contents of the files and folders found at path:
Folder.Contents(path as text) as table
where path is the path to the folder to retrieve contents for.
I will use Folder.Contents() to show the contents of my Power Query Blog folder:
The M code I have used is
=Folder.Contents("C:\Users\kathr\OneDrive\Documents\PQ_StandardExpenses\PQ Blog")
When I execute the code, I get the following screen:
This is a table of the names and some of the attributes of the files in the folder I specified. More detailed attributes are also available under the Attributes column, which may be expanded if required.
Folder.Files()
This returns a table containing a row for each file found at a folder path, and subfolders. Each row contains properties of the folder or file and a link to its content.
Folder.Files(path as text) as table
where path is the path to the folder to retrieve contents for.
I will use Folder.Files() to show the contents of the folder above my Power Query Blog folder:
The M code I have used is
=Folder.Files("C:\Users\kathr\OneDrive\Documents\PQ_StandardExpenses")
When I execute the code, I get the following results:
I have reordered the columns to show that Folder.Files() also retrieves information about files in subfolders below the specified folder. This can be compared to the results I get if I use Folder.Contents() on the same folder.
Folder.Contents() only shows me those files and folders that are in the specified folder, and does not interrogate data in the subfolders. Next time I will look at an example which extracts file data according to more specific conditions.
Come back next time for more ways to use Power Query!
