How to Exclude Subfolders in Excel Power Query (Folder.Contents)
Question details
The user wants to extract files only from a specific target folder without pulling in files located in its subfolders.

- 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.
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.
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.
In your Excel workbook, select your existing query from the Queries & Connections pane and click 'Edit' to launch the Power Query 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.
Find the first line of your query steps, which typically looks like: Source = Folder.Files("C:\Your\Folder\Path").
Change the text from Folder.Files to Folder.Contents so that the line now reads: Source = Folder.Contents("C:\Your\Folder\Path").
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.

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. Download WPS Office: Install WPS Office for free from the official website on your preferred device.
- 2. Open Your Spreadsheets: Open your existing Excel workbooks seamlessly in WPS Spreadsheet with perfect formatting.
- 3. Analyze Data Easily: Utilize the intuitive Data tab to filter out unnecessary folders or rows efficiently.

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.




