Power Query Problems

How to Exclude Subfolders in Excel Power Query (Folder.Contents)

Chanuka GeekiyanageChanuka Geekiyanage Oct 15, 2026 871 views

Question details

The user wants to extract files only from a specific target folder without pulling in files located in its subfolders.

How to Exclude Subfolders in Excel Power Query
Product
Excel Power Query
Device & OS
not provided
Scenario
Importing data from a root folder where the default Power Query command pulls unnecessary files from all nested directories.
Observed behavior
The default Folder.Files command recursively searches and lists all files in the selected folder as well as all of its subfolders.
Before you start

Ensure you have the exact file path to your target folder and open the Advanced Editor in your Power Query window so you can safely modify the M code.

Solution 1Recommended

Replace Folder.Files with Folder.Contents

Modify the M code in Power Query to use the Folder.Contents function, which restricts the data retrieval to files directly inside the specified folder.

By default, when you select 'Get Data from Folder' in Excel, Power Query utilizes the Folder.Files function. This command is inherently recursive and will pull every file from the parent directory and all nested subdirectories. Changing this function to Folder.Contents stops this recursive behavior and returns only the immediate contents of the directory.

1
Open the Power Query Editor

In your Excel workbook, select your existing query from the Queries & Connections pane and click 'Edit' to launch the Power Query Editor.

2
Access the Advanced Editor

On the Home tab of the Power Query Editor ribbon, click on the 'Advanced Editor' button to view the underlying M code for your query.

3
Locate the Source Step

Find the first line of your query steps, which typically looks like: Source = Folder.Files("C:\Your\Folder\Path").

4
Modify the Formula

Change the text from Folder.Files to Folder.Contents so that the line now reads: Source = Folder.Contents("C:\Your\Folder\Path").

5
Apply and Filter

Click 'Done' to apply the changes. The preview table will now show both files and subfolders in the root directory. You can use the filter dropdown on the 'Extension' column to uncheck '(null)' and keep only the files.

Replace Folder.Files with Folder.Contents
Understanding the Output: After applying Folder.Contents, subfolders will appear as standard rows labeled 'Folder'. You can easily exclude them using standard column filters before expanding your data.
Free Microsoft Office alternative

Try WPS Office for Efficient Data Management

If you are looking for a highly compatible, fast, and cost-effective way to manage large datasets and spreadsheets without dealing with complex M code, WPS Office provides an exceptional alternative to Microsoft Excel.

  1. 1. Download WPS Office: Install WPS Office for free from the official website on your preferred device.
  2. 2. Open Your Spreadsheets: Open your existing Excel workbooks seamlessly in WPS Spreadsheet with perfect formatting.
  3. 3. Analyze Data Easily: Utilize the intuitive Data tab to filter out unnecessary folders or rows efficiently.
Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Lightweight design ensures incredibly fast startup and smooth data handling.Built-in advanced filtering, sorting, and data consolidation tools for easy data management.Familiar user interface requiring zero learning curve for seamless migration.
microsoft office alternative - wps office

Frequently Asked Questions

What is the exact difference between Folder.Files and Folder.Contents?

Folder.Files is designed to recursively search the target folder and all nested subfolders to return a list of every file. In contrast, Folder.Contents limits the search to the immediate contents of the specified folder, displaying subfolders as distinct row items rather than extracting the files within them.

How do I filter out the subfolders from the query after using Folder.Contents?

Once you apply Folder.Contents, subfolders will appear in your data preview. Simply click the filter dropdown icon on the 'Extension' column and deselect the '(null)' option, or filter the 'Attributes' column to exclude directories, leaving only your target files.

Will changing Folder.Files to Folder.Contents break my existing query steps?

It may require some adjustments. Because Folder.Contents returns a different initial table structure (listing nested folders as items rather than automatically expanding them), subsequent steps that rely on the specific output structure of Folder.Files might result in errors and will need to be recreated or adjusted.