Power Query How to list folder names only NOT files

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
Hello, I am fairly new to Power Query and have searched but can't find a solution - every thing I find is related to listing files, not folders. Is it possible for power query to just list folder names from a path? I can list all files with the path and extract the folder name but removing the duplicates with the voluminous files is taking way too long for some reasons, so if I can just list the folder names instead of all files should be much faster.

Thank You
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,075
it depends on what the structure is, you can:
group whole path if necessary
split FolderPath by \
remove last blank column

eg.
Folder Path.1Folder Path.2Folder Path.3Folder Path.4Folder Path.5Count
D:BaseFolderFolder1
1​
D:BaseFolderFolder2Folder2.1Folder2.1.1
1​
D:BaseFolderFolder2Folder2.1Folder2.1.2
1​
D:BaseFolderFolder2Folder2.1Folder2.1.3
4​
D:BaseFolderFolder2Folder2.2
1​

Code:
[SIZE=1]let
    Source = Folder.Files("D:\BaseFolder"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Folder Path"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Folder Path"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Folder Path", Splitter.SplitTextByDelimiter("\", QuoteStyle.Csv), {"Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Folder Path.6"})
in
    #"Removed Columns"[/SIZE]
 
Last edited:

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
Hello Sandy and thank you for the reply. I think you may be doing something similar to what I was doing. I am not familiar with the method you are using by grouping rows (?) but I am trying it and there appear to be similar problems. First by using "Source = Folder.Files("D:\BaseFolder")" the first thing it does is list all files, I don't want to do that because there are currently 60K plus files, so when you try to do anything after that, things seem to be really slow. Plus another thing I discovered is that if there are no files in any folder, that folder will not be listed, and I want a list of all folders.

So there is no Power Query command to list folders?

Thank You
"
 

billszysz

Active Member
Joined
Feb 26, 2014
Messages
351
Office Version
365
Try this code below.... this is draft only
Code:
let

    Source = Folder.Files(YourPathToStartFolder),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Folder Path"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Niestandardowe", each Table.SelectRows(Folder.Contents([Folder Path]), each [Content] is table)     ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Niestandardowe.1", each if Table.IsEmpty([Niestandardowe]) then {Text.BeforeDelimiter([Folder Path], "\", {0, RelativePosition.FromEnd})} else Table.CombineColumns([Niestandardowe], {"Folder Path", "Name"}, Combiner.CombineTextByDelimiter(""), "New")[New]),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"Niestandardowe.1"}),
    #"Expanded {0}" = Table.ExpandListColumn(#"Removed Other Columns1", "Niestandardowe.1"),
    #"Removed Duplicates1" = Table.Distinct(#"Expanded {0}"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates1",{{"Niestandardowe.1", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Niestandardowe.1", "Foldery"}})
in
    #"Renamed Columns"
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,075
@Zbyszek
it still doesn't contain empty folders ;)

Foldery
D:\BaseFolder\Folder1
D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1
D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2
D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3

 
Last edited:

billszysz

Active Member
Joined
Feb 26, 2014
Messages
351
Office Version
365
Hi sandy :)
I am surprised, how do you know my name? :)))
You are right. When the empty folder exist on the first level then my code doesn't work properly.
Try thsi modification
Code:
let    FPath = Excel.CurrentWorkbook(){[Name="FPath"]}[Content]{0}[Column1],
    FirstRow = Table.FromColumns({{FPath}}, {"Folder Path"}),
    Source = Folder.Files(FPath),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Folder Path"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    Combine = Table.Combine({FirstRow, #"Removed Duplicates"}),
    #"Added Custom" = Table.AddColumn(Combine, "Niestandardowe", each Table.SelectRows(Folder.Contents([Folder Path]), each [Content] is table)     ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Niestandardowe.1", each if Table.IsEmpty([Niestandardowe]) then {Text.BeforeDelimiter([Folder Path], "\", {0, RelativePosition.FromEnd})} else Table.CombineColumns([Niestandardowe], {"Folder Path", "Name"}, Combiner.CombineTextByDelimiter(""), "New")[New]),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"Niestandardowe.1"}),
    #"Expanded {0}" = Table.ExpandListColumn(#"Removed Other Columns1", "Niestandardowe.1"),
    #"Removed Duplicates1" = Table.Distinct(#"Expanded {0}"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates1",{{"Niestandardowe.1", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Niestandardowe.1", "Folder Path"}}),
    Custom1 = Table.Combine({FirstRow, #"Renamed Columns"})
in
    Custom1
where FPath is named range (one cell) in excel sheet.

Cheers :))
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,075
range name: FPath (blank cell)
Expand (0) = Expression.Error: We cannot convert the value null to type Text.

I am surprised, how do you know my name?
I know more :LOL:
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,075
ok my mistake with empty cell but i can't see this : D:\BaseFolder\Folder2\[highlight]Folder2.2[/highlight]



I c only:
Folder Path
D:\BaseFolder
D:\BaseFolder\Folder1
D:\BaseFolder\Folder2
D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1
D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2
D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3
 
Last edited:

billszysz

Active Member
Joined
Feb 26, 2014
Messages
351
Office Version
365
I am leaving now for the weekend but I think this can be solved using List.Generate and Folder.Contents (without Folder.Files)
Maybe on Sunday I'll try to write some other code.
Cheers :))
 

Watch MrExcel Video

Forum statistics

Threads
1,096,065
Messages
5,448,185
Members
405,490
Latest member
Larry of Oz

This Week's Hot Topics

Top