Please note javascript is required for full website functionality.
MVP

Blog

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!

Newsletter