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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
112
chriscorpion786
C
C
  • Locked
  • Question Question
Refresh Errors in Power BI Desktop loading queries
Replies
1
Views
89
Fluff
Fluff
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
262
Matt Allington
M
K
  • Question Question
Power query - stopping refresh if no data available
Replies
2
Views
139
Kariba
K

Forum statistics

Threads
1,223,275
Messages
6,171,126
Members
452,381
Latest member
Nova88

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