# Power Query How to list folder names only NOT files



## Joyner (Apr 11, 2019)

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 (Apr 11, 2019)

it depends on what the structure is, you can:
group whole path if necessary
split FolderPath by \
remove last blank column

eg.

*Folder Path.1**Folder Path.2**Folder Path.3**Folder Path.4**Folder Path.5**Count*D:BaseFolderFolder11​D:BaseFolderFolder2Folder2.1Folder2.1.11​D:BaseFolderFolder2Folder2.1Folder2.1.21​D:BaseFolderFolder2Folder2.1Folder2.1.34​D:BaseFolderFolder2Folder2.21​


```
[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 (Apr 11, 2019)

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 (Apr 11, 2019)

as far as I know - no


----------



## billszysz (Apr 12, 2019)

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 (Apr 12, 2019)

@Zbyszek
it still doesn't contain empty folders 


*Foldery*D:\BaseFolder\Folder1D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3


----------



## billszysz (Apr 12, 2019)

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 (Apr 12, 2019)

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 (Apr 12, 2019)

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:\BaseFolderD:\BaseFolder\Folder1D:\BaseFolder\Folder2D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3


----------



## billszysz (Apr 12, 2019)

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 )


----------



## Joyner (Apr 11, 2019)

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 (Apr 12, 2019)

sure, maybe I'll fight with this

cya


----------



## Joyner (Apr 12, 2019)

Wow, thank you Bill and Sandy, I am looking at your other code now to see what it is doing.  Thank you so much for the help.


----------



## sandy666 (Apr 12, 2019)

I got this (all folders are empty)


*Folders*D:\BaseFolder\Folder1\D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3D:\BaseFolder\Folder2\Folder2.2\

but this is brute-force so I'll try with full automated way


----------



## Joyner (Apr 12, 2019)

Hello Sandy and thank you again.  If by "full automated way" you mean VBA, I was able to get some code, and it is much faster than the PQ way I tried. But my problem is, certain powers that be don't like the VBA /macro solutions even though it may be better.  So I am trying Power Query, hopefully a fast way can be found. Thanks.


----------



## sandy666 (Apr 12, 2019)

Ha ha ha, I am far far *FAR* away from vba


----------



## sandy666 (Apr 12, 2019)

btw. you can use cmd with this command: D:\BaseFolder>*dir /A /D /B /S* or *dir /A /D /B /S > folders.txt*

and get the result without files:


```
[SIZE=1]D:\BaseFolder\Folder1
D:\BaseFolder\Folder2
D:\BaseFolder\Folder2\Folder2.1
D:\BaseFolder\Folder2\Folder2.2
D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1
D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2
D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3[/SIZE]
```

much much faster


----------



## Joyner (Apr 12, 2019)

Sandy, Sorry but I don't understand that, where and how do I use that?  Thanks


----------



## sandy666 (Apr 12, 2019)

use Run from the Start (I don't know how it looks in Win10) and type there cmd.exe
you'll see black window with prompt







and type there full path to the basic folder (eg. D:\BasicFolder)
then use command from above

something like: C:\>dir D:\BaseFolder /A /D /B /S
or
C:\>dir D:\BaseFolder /A /D /B /S > folders.txt

but ... if you know nothing about DOS commands leave it and wait for M-Code


----------



## billszysz (Apr 16, 2019)

As I promised (but it is draft only).

```
let    fxNewPaths = (t as table, i as number) =>
        let
            iActual = "L" & Text.From(i),
            iNext = "L" & Text.From(i+1),
            AddCol1 = Table.AddColumn(t, "Lx", each try Table.SelectColumns(Table.AddColumn(Table.SelectRows(Folder.Contents(Record.Field(_, iActual)), each [Content] is table)[[Folder Path],[Name]], iNext, each [Folder Path] & [Name]), {iNext}) otherwise null),
            Expand = Table.ExpandTableColumn(AddCol1, "Lx", {iNext})
        in
            Expand,


    FPath = Excel.CurrentWorkbook(){[Name="FPath"]}[Content]{0}[Column1],
    FirstRow = Table.FromColumns({{FPath}}, {"L0"}),
    LiG = List.Generate(() => [FR = FirstRow, i = 0],
                    each List.NonNullCount( Table.Column([FR], "L"& Text.From([i]))) <> 0,
                    each [FR = fxNewPaths([FR], [i]), i = [i]+1],
                    each [FR]),
    TBL = List.Last(LiG),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(TBL, {}, "Attribute", "Folder Path"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Folder Path", Order.Ascending}})
in
    #"Sorted Rows"
```

FPath is (as in my previous post) a named cell in the workbook that contains the path to the start folder.


----------



## sandy666 (Apr 16, 2019)

Wow !


----------



## Joyner (Apr 11, 2019)

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 (Apr 16, 2019)

just for fun I added one line:

```
[SIZE=1]Level = Table.AddColumn(#"Sorted Rows", "Level", each List.Count(Text.PositionOf([Folder Path],"\", Occurrence.All)))[/SIZE]
```


----------



## Joyner (Apr 16, 2019)

Hello Bill and Sandy, and thank you again. I am trying to run the code, maybe I'm doing the FPath part wrong, because it doesn't seem to be picking the path up.  Is the FPath simply a Defined Name on any sheet, or does it need to be a table formatted a certain way?  Thanks


----------



## sandy666 (Apr 16, 2019)

look at


----------



## mikemck (Apr 17, 2019)

This article might be just what you are looking for:

https://www.sumproduct.com/blog/article/power-query-blogs/power-query-find-the-folder


----------



## Joyner (Apr 22, 2019)

Sorry for the late response I have been tied up and trying the solutions.  Thanks to everyone for the help.


----------



## Joyner (Apr 22, 2019)

sandy666 said:


> just for fun I added one line:
> 
> ```
> [SIZE=1]Level = Table.AddColumn(#"Sorted Rows", "Level", each List.Count(Text.PositionOf([Folder Path],"\", Occurrence.All)))[/SIZE]
> ```



Sandy, I wasn't sure how this got added and what it does.


----------



## Joyner (Apr 22, 2019)

billszysz said:


> As I promised (but it is draft only).
> 
> ```
> let    fxNewPaths = (t as table, i as number) =>
> ...



Thank you Bill, I have gotten this to work, but many times it still seems to take a long time to run.  Maybe it's just my computer.


----------



## Joyner (Apr 22, 2019)

mikemck said:


> This article might be just what you are looking for:
> 
> https://www.sumproduct.com/blog/article/power-query-blogs/power-query-find-the-folder



Thanks Mike, this seems to work and seems to run fast.


----------



## Joyner (Apr 22, 2019)

Does anyone know, if you can somehow hyperlink the path without VBA.  I have searched and it seems that with Excel (Not Power BI) that this is not possible.  Thank You.


----------



## sandy666 (Apr 22, 2019)

add prefix: HYPERLINK("
then add suffix: ")
then add prefix: =
load to the sheet
then replace = with =
*
edit:*
but after refresh you'll need repeat last line


----------



## Joyner (Apr 11, 2019)

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 (Apr 22, 2019)

Joyner said:


> Sandy, I wasn't sure how this got added and what it does.




*Folder Path**Level*D:\BaseFolder1​D:\BaseFolder\Folder12​D:\BaseFolder\Folder22​D:\BaseFolder\Folder2\Folder2.13​D:\BaseFolder\Folder2\Folder2.1\Folder2.1.14​D:\BaseFolder\Folder2\Folder2.1\Folder2.1.24​D:\BaseFolder\Folder2\Folder2.1\Folder2.1.34​D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3\Folder2.1.3.15​D:\BaseFolder\Folder2\Folder2.23​


----------



## Joyner (Apr 22, 2019)

Thanks Sandy, that works, but wish there was a way to automate the hyperlinks with Power Query - maybe next version...


----------



## sandy666 (Apr 22, 2019)

maybe


----------

