Thanks Thanks:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: Power Query How to list folder names only NOT files

  1. #1
    Board Regular
    Join Date
    Nov 2005
    Location
    Clearwater, Florida
    Posts
    1,187
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query How to list folder names only NOT files


    Power Query How to list folder names only NOT files
    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
    forum use guidelines; forum rules; terms of use; FAQs Use code tags [ Code ] your code here [ /Code ] Try searching for your answer first, see how

    Work - 32 bit Office 2016 Win10 .... Home - Office 365 Win10

    I solve for X but don't know Y


  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,678
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Power Query How to list folder names only NOT files

    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: BaseFolder Folder1
    1
    D: BaseFolder Folder2 Folder2.1 Folder2.1.1
    1
    D: BaseFolder Folder2 Folder2.1 Folder2.1.2
    1
    D: BaseFolder Folder2 Folder2.1 Folder2.1.3
    4
    D: BaseFolder Folder2 Folder2.2
    1


    Code:
    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"
    Last edited by sandy666; Apr 11th, 2019 at 12:21 PM.
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Nov 2005
    Location
    Clearwater, Florida
    Posts
    1,187
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query How to list folder names only NOT files

    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
    "
    forum use guidelines; forum rules; terms of use; FAQs Use code tags [ Code ] your code here [ /Code ] Try searching for your answer first, see how

    Work - 32 bit Office 2016 Win10 .... Home - Office 365 Win10

    I solve for X but don't know Y


  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,678
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query How to list folder names only NOT files

    as far as I know - no
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Feb 2014
    Posts
    339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query How to list folder names only NOT files

    Try this code below.... this is draft only
    Code:
    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"

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,678
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Power Query How to list folder names only NOT files

    @Zbyszek
    it still doesn't contain empty folders

    Foldery
    D:\BaseFolder\Folder1
    D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1
    D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2
    D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3


    Last edited by sandy666; Apr 12th, 2019 at 05:00 AM.
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #7
    Board Regular
    Join Date
    Feb 2014
    Posts
    339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query How to list folder names only NOT files

    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
    Code:
    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 )

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,678
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Power Query How to list folder names only NOT files

    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
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,678
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Power Query How to list folder names only NOT files

    ok my mistake with empty cell but i can't see this : D:\BaseFolder\Folder2\Folder2.2



    I c only:
    Folder Path
    D:\BaseFolder
    D:\BaseFolder\Folder1
    D:\BaseFolder\Folder2
    D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1
    D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2
    D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3
    Last edited by sandy666; Apr 12th, 2019 at 06:58 AM.
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #10
    Board Regular
    Join Date
    Feb 2014
    Posts
    339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query How to list folder names only NOT files

    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 )

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •