Ah, OK, I didn't realize that if it would work in Power BI it would also work in Excel.

I did open your workbook and change the directory. It does give me a nifty listing of just the main project folders on the drive. My wrinkle is that I actually need the subfolders for invoices in each of the project folders. I am able to do that in Power Query, except for the few Invoices files that don't yet have invoices in them. I'm also able to add a column that trims the long folder just down to the project number, which gives me exactly what I need except for those few empty ones. I have tinkered around with the setup you shared, and I can only get it to list the main folders on the drive.



What I do to get the subfolders is get a list of everything, then filter for where the folder path ends in "Invoices", then filter to uniques. Then I duplicate the folder path column and trim to just the project number, giving me the XLOOKUP info that I need to do the "auto-filing" VBA that Paul helped me figure out here (https://www.msofficeforums.com/excel...using-vba.html)

If I'm missing something and there's a way to get into subfolders, I'll keep trying.

Thanks!

Ann

If you've done (or started) this with a powershell command, what was the command, because command you've shown only returns the top level of folders?
Are we talking only one level down of subfolders?


Otherwise I'll have an explore in the coming days.

Yes, for the most part they are usually just one level down. It'd be like \\Projects\\J1234G State Road 567 at Pine Street Intersection\Invoices\

You're right, I think I just grabbed the wrong Powershell command line that I keep in Outlook Notes. That's the one I used to get the names of all the PDFs of the invoices, which I have changed to doing by PQ. The Powershell command that yields literally everything in the main folder (recursive) is: And that yields a lot of rows of data, which I then filter in Excel for anything that ends in "Invoice" and end up concatenating the rest of the file path in.

I suppose the other thing I could do is search out the few ...\Invoices\ subfolders that are empty and place a small dummy file in them?

I think I'm like 90% there, so I'm not that worried about it.