Thanks Thanks:  0
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 33

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

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

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


    Power Query How to list folder names only NOT files
    sure, maybe I'll fight with this

    cya
    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. #12
    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

    Wow, thank you Bill and Sandy, I am looking at your other code now to see what it is doing. Thank you so much for the help.
    Last edited by Joyner; Apr 12th, 2019 at 08:46 AM.
    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. #13
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

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

    I got this (all folders are empty)

    Folders
    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
    D:\BaseFolder\Folder2\Folder2.2\


    but this is brute-force so I'll try with full automated way
    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. #14
    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 again. If by "full automated way" you mean VBA, I was able to get some code, and it is much faster than the PQ way I tried. But my problem is, certain powers that be don't like the VBA /macro solutions even though it may be better. So I am trying Power Query, hopefully a fast way can be found. Thanks.
    Last edited by Joyner; Apr 12th, 2019 at 10:20 AM.
    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


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

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

    Ha ha ha, I am far far FAR away from vba
    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

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

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

    btw. you can use cmd with this command: D:\BaseFolder>dir /A /D /B /S or dir /A /D /B /S > folders.txt

    and get the result without files:

    Code:
    D:\BaseFolder\Folder1
    D:\BaseFolder\Folder2
    D:\BaseFolder\Folder2\Folder2.1
    D:\BaseFolder\Folder2\Folder2.2
    D:\BaseFolder\Folder2\Folder2.1\Folder2.1.1
    D:\BaseFolder\Folder2\Folder2.1\Folder2.1.2
    D:\BaseFolder\Folder2\Folder2.1\Folder2.1.3
    much much faster
    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. #17
    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

    Sandy, Sorry but I don't understand that, where and how do I use that? 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


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

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

    use Run from the Start (I don't know how it looks in Win10) and type there cmd.exe
    you'll see black window with prompt



    and type there full path to the basic folder (eg. D:\BasicFolder)
    then use command from above

    something like: C:\>dir D:\BaseFolder /A /D /B /S
    or
    C:\>dir D:\BaseFolder /A /D /B /S > folders.txt

    but ... if you know nothing about DOS commands leave it and wait for M-Code
    Last edited by sandy666; Apr 12th, 2019 at 12:03 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

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

    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.

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

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

    Wow !
    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
  •