How to export a folder and its subfolders in just 5 steps
- Load the path of your folder with Power Query
Power Query is the easiest way to connect to a folder
- Keep only a few data
Power Query will returns many information but not all of them are needed
- Load the result into Excel
Once you have finish to manipulate and clean your data, load them info Excel
Step 1: Connect to a folder
- Got to the Data tab
- Click Get Data
- Then From File
- And finally From Folder

- In the following dialog box, click the Browse icon

- A new dialog box shows all the files in the folder and the subfolders selected. Simply click on the button Transform data

Step 2: Add the file's size
The size does not load by default. But the Record column contains some essential data that we will display.
- Click on the arrow in the Attributes column

- Select only the Size field
- Unchecked Use original column name as prefix

Step 3: Keep only 3 columns
We only keep 3 columns
- The path
- The name of the files
- Size
With Power Query, there is a trick to remove and also reorder columns in the same action. Select in this order
- The Folder Path column
- Then the Name column (with the Ctrl key)
- And finally the Size column
- Right-click on the headers
- Select Remove Other Columns

The columns are re-ordered automatically 😉😮
Step 4: Delete the initial path
Power Query always displays the entire initial path. But we can easily remove the first part of the string like this
- Right-click on the header of the Folder Path column
- Select the Replace values option

- Enter the search string (here C:\Users\fred_\OneDrive - FlgConsulting\20_EXCEL\)
- Leave the replacement string empty

Step 5: Split all the subfolders
Because we don't know how many subfolders we can have, we don't know how many times we will have the symbol \ in a string. So this task looks very complex to do .... but not with Power Query 😊😉
- Right-click on the header of the Folder Path column
- Select the option Split Column
- Then By Delimiter

- Then, select the option Custom in the dropdown list
- Write the delimiter \
- VERY IMPORTANT, use the option Each occurrence of the delimiter

And then, immediately, Power Query creates as many columns as you have sub-folders In this example, the root folder has 4 levels.

To finish, we return the result to Excel (Home > Close & Load)

And then, you load in your spreadsheet, all the subfolders, files, and size 😀👍

Sunburst chart
The best way to visualize the hierarchy of your folder, subfolders, files, and size is to create a sunburst chart.

07/09/2023 @ 12:55
This is hands down the best tutorial I found. Should be made a template.
Thank you so much for this!! Super helpful and straightforward.
07/09/2023 @ 13:01
Thanks you so much for your nice comment Luca
11/07/2023 @ 19:40
There is no transform data button. What version of Excell is this for?
12/07/2023 @ 02:40
Hi, you don't have the Transform Data button? That's weird. I did my tuto with Excel 365 2 years ago, and I have checked, the button is still there.
What is your Excel version? Which buttons do you have?
Thanks
25/08/2022 @ 07:01
Hello,
Great contents are there in this website. So many learning materials are there. Bui I am unable to find the practice data for those exercises. Please provide the way to download the data otherwise not able to practice properly.