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]

sure, maybe I'll fight with this

cya

 

[Joyner]

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]

I got this (all folders are empty)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Folders[/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]

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

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

 

[Joyner]

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]

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

 

[sandy666]

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]

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

 

[sandy666]

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]

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]

Wow !