Power Query Problems

How to List SharePoint Files in Nested Folders with Excel Power Query

John Wilson Oct 1, 2026 869 views

Question details

The user wants to retrieve a list of files from nested folders in SharePoint using Excel Power Query while attempting to retain advanced SharePoint metadata.

How to List SharePoint Files in Nested Folders with Excel Power Query

Product: Excel
Device & OS: not provided
Scenario: Extracting file lists and their associated metadata from SharePoint subfolders and nested folders into an Excel spreadsheet.
Observed behavior: The SharePoint Folder connector successfully retrieves files from nested folders, but it does not expose retention labels, record status, or other advanced metadata that is normally available through the SharePoint Online List connector.

Before you start

Ensure you have the correct URL to your SharePoint site and the appropriate access permissions to view the nested folders and file metadata.

Solution 1Recommended

Extract Files Using the SharePoint Folder Connector

Use the built-in SharePoint Folder connector in Excel to pull all files from nested directories into one query.

The SharePoint Folder connector is designed to traverse directories and retrieve files from subfolders automatically. However, it is important to note that it exposes fewer metadata fields compared to the SharePoint Online List connector.

Open Get Data

Open Excel, navigate to the 'Data' tab on the ribbon, and click on 'Get Data'.

Select SharePoint Folder

Go to 'From File' (or 'From Online Services', depending on your Excel version) and select 'From SharePoint Folder'.

Enter SharePoint URL

Paste your root SharePoint site URL into the prompt and click 'OK' to authenticate and load the directory.

Transform Data

Click 'Transform Data' to open the Power Query Editor. Here, you can filter the 'Folder Path' column to isolate the specific nested folders you want to list.

Extract Files Using the SharePoint Folder Connector

Metadata Limitations: The SharePoint Folder connector does not currently expose retention labels, record status, or record dates. It retrieves fewer metadata fields than the SharePoint Online List connector.

Solution 2

Submit Feedback for Missing Metadata

Request support from Microsoft if the required SharePoint metadata is unavailable through the standard folder connector.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If you frequently encounter limitations or complex troubleshooting in Microsoft Excel, consider switching to WPS Office. It provides a lightweight, free, and highly compatible alternative for managing spreadsheets and data analysis with ease.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
2. Install and Open: Run the installer, open WPS Spreadsheet, and seamlessly import your existing .xlsx data files.
3. Manage Data Easily: Enjoy a familiar interface to organize, filter, and manage your data efficiently without complex connector limitations.

Fully compatible with Microsoft Excel (.xlsx) formatsLightweight installation with fast loading timesFamiliar spreadsheet user interface with zero learning curveBuilt-in PDF tools and multi-platform cloud support

microsoft office alternative - wps office

Frequently Asked Questions

Can I get SharePoint retention labels using the Power Query Folder connector?

No, the SharePoint Folder connector retrieves files from nested folders but does not expose advanced metadata like retention labels, record status, or record dates. You would need to use the SharePoint Online List connector, though it does not handle folder traversal in the same way.

What is the difference between the SharePoint Folder and List connectors in Power Query?

The SharePoint Folder connector is optimized for retrieving file contents and basic attributes from entire directory structures, including subfolders. The SharePoint List connector is designed for list items and exposes much richer SharePoint metadata, but handling file binaries inside nested folders is more complex.

How do I filter specific nested subfolders in Power Query?

After connecting to the SharePoint Folder, click 'Transform Data'. Locate the 'Folder Path' column in the Power Query Editor, click the filter dropdown, and apply text filters (such as 'Contains' or 'Begins With') to include only the nested folders you need.

Excel & Data Problems