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]

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

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

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

1​

[/td][/tr]

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

2​

[/td][/tr]

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

2​

[/td][/tr]

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

3​

[/td][/tr]

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

4​

[/td][/tr]

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

4​

[/td][/tr]

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

4​

[/td][/tr]

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

5​

[/td][/tr]

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

3​

[/td][/tr]
[/table]

 

[Joyner]

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

 

[sandy666]

maybe

 

[pjmorris]

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.

Not sure if you'll see this, but I was contemplating a recursive solution - and you've provided it. Absolutely perfect, many thanks.