How to extract a folder and its subfolders in just 5 steps with Excel
- 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 return many information but not all of them are needed
- Load the result into Excel
Once you have finished manipulating and cleaning your data, load them into Excel
Step 1: Connect to a folder with Power Query
To Extract folders and subfolders with Excel, the best way is to use Power Query
- 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 names 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)
- Finally, you have extracted all the folders and subfolders in Excel
Related Articles
- Sunburst Chart with Excel (the best way to represent a hierarchy)
- Separate Date and Time with Power Query
- DATEDIFF for POWER QUERY
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.