Power Query How to list folder names only NOT files

J

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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
it depends on what the structure is, you can:
group whole path if necessary
split FolderPath by \
remove last blank column

eg.
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Folder Path.1[/td][td=bgcolor:#70AD47]Folder Path.2[/td][td=bgcolor:#70AD47]Folder Path.3[/td][td=bgcolor:#70AD47]Folder Path.4[/td][td=bgcolor:#70AD47]Folder Path.5[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]D:[/td][td=bgcolor:#E2EFDA]BaseFolder[/td][td=bgcolor:#E2EFDA]Folder1[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D:[/td][td]BaseFolder[/td][td]Folder2[/td][td]Folder2.1[/td][td]Folder2.1.1[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]D:[/td][td=bgcolor:#E2EFDA]BaseFolder[/td][td=bgcolor:#E2EFDA]Folder2[/td][td=bgcolor:#E2EFDA]Folder2.1[/td][td=bgcolor:#E2EFDA]Folder2.1.2[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D:[/td][td]BaseFolder[/td][td]Folder2[/td][td]Folder2.1[/td][td]Folder2.1.3[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]D:[/td][td=bgcolor:#E2EFDA]BaseFolder[/td][td=bgcolor:#E2EFDA]Folder2[/td][td=bgcolor:#E2EFDA]Folder2.2[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]
[/table]

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:
Upvote 0
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
"
 
Upvote 0
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"
 
Upvote 0
@Zbyszek
it still doesn't contain empty folders ;)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Foldery[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]D:\BaseFolder\Folder1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3[/td][/tr]
[/table]

folders.jpg
 
Last edited:
Upvote 0
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 :-))
 
Upvote 0
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?
Click to expand...
I know more :laugh:
 
Upvote 0
ok my mistake with empty cell but i can't see this : D:\BaseFolder\Folder2\[highlight]Folder2.2[/highlight]

screenshot-7.png


I c only:
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Folder Path[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]D:\BaseFolder[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D:\BaseFolder\Folder1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]D:\BaseFolder\Folder2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3[/td][/tr]
[/table]
 
Last edited:
Upvote 0
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 :-))
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Refresh errors after normalizing flat files coming from a folder in Power BI
Replies
3
Views
144
chriscorpion786
C
C
  • Locked
  • Question Question
Refresh Errors in Power BI Desktop loading queries
Replies
1
Views
110
Fluff
Fluff
M
  • Solved
New to Excel Power Query - Need to pull partial file name with most recent date
Replies
3
Views
99
datatronics505
datatronics505
earthworm
  • Question Question
Importing data from Folder through Power Query
2
Replies
12
Views
1K
earthworm
earthworm
U
  • Question Question
How to combine multiple Power Query tables/tabs together?
Replies
3
Views
271
Matt Allington
M

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top