Power Query How to list folder names only NOT files

[Joyner]

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

 

[sandy666]

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]

[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]

 

[Joyner]

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
"

 

[sandy666]

as far as I know - no

 

[billszysz]

Try this code below.... this is draft only

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]

@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]

 

[billszysz]

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

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]

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

 

[sandy666]

ok my mistake with empty cell but i can't see this : D:\BaseFolder\Folder2\[highlight]Folder2.2[/highlight]

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]

 

[billszysz]

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 )