Power Query How to list folder names only NOT files

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,083
just for fun I added one line:
Code:
[SIZE=1]Level = Table.AddColumn(#"Sorted Rows", "Level", each List.Count(Text.PositionOf([Folder Path],"\", Occurrence.All)))[/SIZE]
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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
 

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
Sorry for the late response I have been tied up and trying the solutions. Thanks to everyone for the help.
 

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
just for fun I added one line:
Code:
[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

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
As I promised (but it is draft only).
Code:
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

Well-known Member
Joined
Nov 15, 2005
Messages
1,202
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,083
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,096,240
Messages
5,449,186
Members
405,556
Latest member
dougbohr

This Week's Hot Topics

Top