Power Query

Extract folder and subfolders with Excel

Reading Time: 3 minutes

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

Export of the folder and subfolders

Step 1: Connect to a directory

  1. Got to the Data tab
  2. Click Get Data
  3. Then From File
  4. And finally From Folder
Menu Import Folder
  1. In the following dialog box, click the Browse icon
Select the folder to import
  1. A new dialog box shows all the files in the folder and subfolders selected. Simply click on button Transform data
All the files in the folder and subfolders

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
Deploy the column Attributes
  1. Select only the Size field and unchecked Use original column name as prefix
Select the Size field

Step 3: Keep only 3 columns

We only keep 3 columns

  • The path
  • The name of the files
  • Size

With Power Query, there is trick to remove and also reorder column 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
Only 3 columns kept

The columns are re-ordered automatically 😉😮

Step 4: Delete the initial path

Power Query always display 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
Power Query menu to replace values
  1. Enter the search string (here C:\Users\fred_\OneDrive - FlgConsulting\20_EXCEL\)
  2. Leave the replacement string empty
Power Query dialog box to replace

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
Power Query option split column by delimiter
  1. Then, select the option Custom in the dropdown list
  2. Write your delimiter ; here \
  3. VERY IMPORTANT, use the option Each occurrence of the delimiter
Option to split the column

And then, immediately, Power Query create 4 columns representing the 4 levels of hierarchy in the subfolders.

Creation of 4 columns after the operation to split column

Then, we just have to return this result to Excel (Home > Close & Load)

Power Query menu Close and Load

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

Export of the folder and subfolders

Sunburst chart

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

Sunburst Chart

Related posts

Import Many Workbooks in a single one

Frédéric LE GUEN

Split Date and Time

Frédéric LE GUEN

Leave a Comment