Thanks Thanks:  0
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 33

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

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

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


    Power Query How to list folder names only NOT files
    just for fun I added one line:
    Code:
    Level = Table.AddColumn(#"Sorted Rows", "Level", each List.Count(Text.PositionOf([Folder Path],"\", Occurrence.All)))
    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

  2. #22
    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 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
    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


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

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

    look at

    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

  4. #24
    New Member
    Join Date
    Apr 2016
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    This article might be just what you are looking for:

    https://www.sumproduct.com/blog/arti...ind-the-folder

  5. #25
    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

    Sorry for the late response I have been tied up and trying the solutions. Thanks to everyone for the help.
    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


  6. #26
    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

    Quote Originally Posted by sandy666 View Post
    just for fun I added one line:
    Code:
    Level = Table.AddColumn(#"Sorted Rows", "Level", each List.Count(Text.PositionOf([Folder Path],"\", Occurrence.All)))
    Sandy, I wasn't sure how this got added and what it does.
    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


  7. #27
    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

    Quote Originally Posted by billszysz View Post
    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.
    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


  8. #28
    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

    Quote Originally Posted by mikemck View Post
    This article might be just what you are looking for:

    https://www.sumproduct.com/blog/arti...ind-the-folder
    Thanks Mike, this seems to work and seems to run fast.
    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


  9. #29
    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

    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.
    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


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

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

    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 by sandy666; Apr 22nd, 2019 at 09:16 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

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
  •