Create a SharePoint Folder and File Directory with Excel Power Query
Question details
Create a refreshable, indented directory of SharePoint folders and files by using Power Query.
- Product
- Microsoft Excel Power Query with SharePoint
- Device & OS
- not provided
- Scenario
- The user wants to display files and nested folder levels from a SharePoint site in a structured Excel directory.
- Observed behavior
- The goal is a directory that retains folder paths and file names, shows hierarchy through indentation, and updates after SharePoint changes.
Make sure you have permission to open the target SharePoint site and its document library. Copy the site URL only, such as https://contoso.sharepoint.com/sites/TeamSite, rather than a link to an individual file or folder.
Use SharePoint.Files to create a refreshable file directory
Use this method when you need a site-wide or library-wide list of files and want to build the folder hierarchy from the Folder Path column.
SharePoint.Files returns files recursively, including their names and folder paths. You can use the path to calculate each file's nesting level, add a visual indent, filter unnecessary items, and then load the directory into Excel.
In Excel, select Data > Get Data > From Online Services > From SharePoint Online List or use Data > Get Data > From Other Sources > From SharePoint Folder, depending on your Excel version. Enter the SharePoint site URL and sign in with your organizational account.
In the Power Query Navigator, select the available files source, or in Power Query create a blank query and use SharePoint.Files with the site URL. Keep the Name, Folder Path, Extension, Date modified, and Content columns as needed.
In Power Query Editor, use the filter button on Name, Extension, or Folder Path to exclude temporary files, system folders, or file types that should not appear. Select Home > Choose Columns to retain only the directory fields you need.
Select Add Column > Custom Column and create a folder-level value from Folder Path. For SharePoint URL paths, count separators with a formula such as List.Count(Text.Split([Folder Path], "/")); if your path uses backslashes, replace "/" with "\". Adjust the result if the site URL contributes fixed path segments.
Select Add Column > Custom Column and combine an indentation prefix with the file name, for example Text.Repeat(" ", [Folder Level]) & [Name]. Use a repeated dash, bullet, or vertical bar instead of spaces if you want the hierarchy to be more visible.
Sort Folder Path in ascending order, then sort Name if needed. Select Home > Close & Load to load the directory to an Excel worksheet. Later, select Data > Refresh All to update it after the SharePoint structure changes.
Use SharePoint.Contents to browse a specific document library
Use this method when you want to navigate to one library or one known folder before loading its contents, rather than returning every file from the site.
Work with SharePoint directory exports in WPS Spreadsheets
If Microsoft Excel Power Query or its SharePoint connector is unavailable or causing issues, WPS Office provides a free, lightweight spreadsheet alternative for opening, organizing, filtering, sorting, and presenting exported SharePoint directory data. Its familiar spreadsheet interface supports a smooth transition from Excel workflows.
- 1. Open the exported directory: Launch WPS Spreadsheets and select Menu > Open to open the .xlsx, .xls, or .csv file exported from Excel or SharePoint.
- 2. Organize directory columns: Select the Folder Path column and use Data > Sort to arrange records by path, then sort the Name column to group items consistently.
- 3. Apply filters to hide unwanted entries: Select the header row and choose Data > AutoFilter, then filter file extensions, folders, or names that should not be displayed.
- 4. Format the hierarchy for readability: Select the display-name column and use Home > Increase Indent or add repeated spaces and symbols in a helper column to make nested items easier to scan.
Frequently Asked Questions
Should I use SharePoint.Files or SharePoint.Contents for a folder directory?
Use SharePoint.Files when you need a recursive list of files across the site or library. Use SharePoint.Contents when you want to navigate to a particular document library or folder before selecting content.
Why does my Power Query result show files but not separate folders?
SharePoint.Files is file-focused and returns each file's Folder Path. To add folder rows, duplicate or reference the query, keep Folder Path, remove duplicates, format the path as a folder name, and append it to the file query.
How can I indent items according to their SharePoint folder level?
In Power Query Editor, choose Add Column > Custom Column. Split or count the separators in Folder Path to create a level number, then use Text.Repeat with spaces or another character and concatenate it with the Name column.
How do I refresh the SharePoint directory after folders change?
After loading the query to Excel, select Data > Refresh All. Power Query reconnects to the same SharePoint source and reloads the current files and paths, subject to your account permissions.
What SharePoint URL should I enter in the connector?
Enter the root site URL, such as https://contoso.sharepoint.com/sites/Marketing. Do not use a copied URL for an individual document, sharing link, or deeply nested folder unless the connector specifically requests it.
