This article will explain how to export an entire folder and its subfolders in just 5 steps. For this, we will use Power Query.

Step 1: Connect to a directory
- 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.



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.