Home » Power Query » Extract folder and subfolders with Excel

Extract folder and subfolders with Excel

Reading time: 3 minutes
Last Updated on 18/05/2023 by Frédéric LE GUEN

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 the subfolders selected. Simply click on the 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
  2. 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 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
Only 3 columns kept

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
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 the delimiter \
  3. VERY IMPORTANT, use the option Each occurrence of the delimiter
Option to split the column

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

Creation of 4 columns after the operation to split column

To finish, we return the 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

1 Comment

  1. Abhishek
    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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Extract folder and subfolders with Excel

Reading time: 3 minutes
Last Updated on 18/05/2023 by Frédéric LE GUEN

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 the subfolders selected. Simply click on the 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
  2. 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 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
Only 3 columns kept

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
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 the delimiter \
  3. VERY IMPORTANT, use the option Each occurrence of the delimiter
Option to split the column

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

Creation of 4 columns after the operation to split column

To finish, we return the 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

1 Comment

  1. Abhishek
    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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *