Step 1: Connect to a folder

1. Got to the Data tab
2. Click Get Data
3. Then From File
4. And finally From Folder

5. In the following dialog box, click the Browse icon

6. 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.

1. Click on the arrow in the Attributes column

2. Select only the Size field
3. 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

1. The Folder Path column
2. Then the Name column (with the Ctrl key)
3. And finally the Size  column
4. Right-click on the headers
5. 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

1. Right-click on the header of the Folder Path column
2. Select the Replace values option

3. Enter the search string (here C:\Users\fred_\OneDrive - FlgConsulting\20_EXCEL\)
4. 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 😊😉

1. Right-click on the header of the Folder Path column
2. Select the option Split Column
3. Then By Delimiter

1. Then, select the option Custom in the dropdown list
2. Write the delimiter \
3. 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.