Hello,

I'm trying to use Power Query within Excel to produce a list of outstanding and completed work. The items that a team needs to work on are in PDF format and sit in folders corresponding to a geographical area. Within each such folder are two subfolders; a "Completed" folder and a "Issues" folder. PDFs in a geographical area folder will get moved into subfolders as they are either completed or found to have issues that will require obtaining an alternative PDF.

What I'm trying to accomplish is producing a list that has a row for each geographic area; and then columns for "Outstanding", Completed" and "Issues".

My issue is that I can find plenty of instruction on how to count the files or how to count the folders, but I need to do both at the same time in order that empty folders will be included on the resulting sheet with a zero result.

There's two other aspects that I can likely figure out myself, which is that the resulting workbook will sit one extra folder level up from the geographic area folders, and that it needs to have the folder structure be dynamic so that it can be dropped into a different set of folders that have been set up the same way and perform a count there. To be more exact, the folder structure will be as follows for the Completed folders, with the workbook I want to create to be dropped into the root folder.pdf

...\<root folder>\Outstanding Work\<geographic area>\Completed

Is there a way to accomplish finding all the folders and subfolders, and then providing a count of the number of PDFs in each folder, even if some folders will be empty?