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]

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]

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]

look at

 

[mikemck]

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]

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

 

[Joyner]

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]

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.

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]

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]

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]

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