Folder.Contents

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Folder.Contents

The Folder.Contents function is written in a language called M, which is used exclusively in Power Query. In this article, we will explore the M code behind the Folder.Contents function and how it works.

Understanding the Folder.Contents Function

Before we dive into the M code behind the Folder.Contents function, let’s first understand what this function does. The Folder.Contents function returns a table that lists all the files in a folder, along with their metadata such as file name, file path, size, and date modified.

The syntax for the Folder.Contents function is as follows:

Folder.Contents(folder_path)

Here, `folder_path` is the path of the folder from which you want to import files.

The M Code Behind the Folder.Contents Function

Now let's take a closer look at the M code behind the Folder.Contents function. When you use the Folder.Contents function, Power Query generates the following M code:

let

Source = Folder.Files(folder_path),

#”Filtered Rows” = Table.SelectRows(Source, each ([Attributes]?[Hidden]? <> true) and ([Attributes]?[ReadOnly]? <> true)),

#”Removed Other Columns” = Table.SelectColumns(#”Filtered Rows”,{“Folder Path”, “Name”, “Extension”, “Date accessed”, “Date modified”, “Date created”, “Attributes”, “Content”}),

#”Expanded Content” = Table.ExpandBinaryColumn(#”Removed Other Columns”, “Content”, {“Content”}, {“Content”})

in

#”Expanded Content”

Let's break down this code and see what each step does.

The first step is to use the `Folder.Files` function to get a list of all the files in the specified folder. This generates a table that includes all the file metadata.

Source = Folder.Files(folder_path)

The next step is to filter out any hidden or read-only files. This is done using the `Table.SelectRows` function.

#”Filtered Rows” = Table.SelectRows(Source, each ([Attributes]?[Hidden]? <> true) and ([Attributes]?[ReadOnly]? <> true))

The third step is to select only the columns we are interested in, which include the file path, file name, file extension, and file metadata such as date accessed, date modified, date created, and attributes.

#”Removed Other Columns” = Table.SelectColumns(#”Filtered Rows”,{“Folder Path”, “Name”, “Extension”, “Date accessed”, “Date modified”, “Date created”, “Attributes”, “Content”})

Finally, the `Table.ExpandBinaryColumn` function is used to expand the `Content` column, which contains the binary data for each file, into separate rows.

#”Expanded Content” = Table.ExpandBinaryColumn(#”Removed Other Columns”, “Content”, {“Content”}, {“Content”})

This generates the final table that is returned by the Folder.Contents function.

In this article, we explored the M code behind the Folder.Contents function in Power Query. We saw how the function uses a series of M functions to retrieve a list of files from a folder and return a table that includes file metadata and binary data. By understanding the M code behind this function, we can better understand how Power Query works and how we can leverage this powerful tool to transform and shape our data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)