How to exclude certain sub-folders when using Get Data From Folder option

alfred

New member
Joined
Mar 16, 2017
Messages
6
Reaction score
0
Points
0
Hi

Appreciate any assistance with above query. Getting an IO error when trying to load data to model. I suspect it may be because some of the sub-folders in the source folder are protected.

Alfred
 
With "Get Data From Folder", you get a navigation table with all files in the folder and its sub folders. In this navigation table, you can deselect folders (folder paths are in the right-most column).
 
Marcel is dead on with this, but I'll add a bit more from a best practice perspective.

When I hit the Get Files From Folder, I'll do a couple of things:
1) change the file extension type to all lower case
2) filter the file type to ONLY the file type I'm expecting to work with
3) filter out any subdirectories I do not want to combine
4) filter out any files that start with ~$ (these are temp files)
5) add any other required filters to get down to just the files I need

I ALWAYS do the first two, just as a matter of good practice (in case some joker throws a Word doc in the folder), but the others can be applied as needed in order to protect against the issue you're experiencing.

I then name that query "FilesList", create a new query by referencing my FilesList query, and combine from there. This practice will set you up best as you roll into the new Combine Binaries experience that may/may not have hit your version of Excel just yet.
 
Actually tried #3 (filtering out sub-folders) but power query performance was so slow.

I guess there are some performance limitations to power query's "get from folder" option, particularly if the source folder is huge and the reports in question are in so many different sub-folders e.g. the data dumps I was after were saved in separate daily subfolders in monthly subfolders in yearly subfolders in the source folder....

As a workaround, I manually copied these files to a single folder and proceeded from there

Thanks again
Alfred
 
Marcel is dead on with this, but I'll add a bit more from a best practice perspective.

When I hit the Get Files From Folder, I'll do a couple of things:
1) change the file extension type to all lower case
2) filter the file type to ONLY the file type I'm expecting to work with
3) filter out any subdirectories I do not want to combine
4) filter out any files that start with ~$ (these are temp files)
5) add any other required filters to get down to just the files I need

I ALWAYS do the first two, just as a matter of good practice (in case some joker throws a Word doc in the folder), but the others can be applied as needed in order to protect against the issue you're experiencing.

I then name that query "FilesList", create a new query by referencing my FilesList query, and combine from there. This practice will set you up best as you roll into the new Combine Binaries experience that may/may not have hit your version of Excel just yet.

Hi Ken...I've read a number of your articles, thanks for what you've done for the community! I have a slightly different nuance to this...Is there any way to limit the folder search to the specified folder w/o subfolders as part of the initial "Source" step rather than a filter subsequent to "Source?"
 
Hi Ken...I've read a number of your articles, thanks for what you've done for the community! I have a slightly different nuance to this...Is there any way to limit the folder search to the specified folder w/o subfolders as part of the initial "Source" step rather than a filter subsequent to "Source?"
If you don't want subfolders, you would filter the Folder Path to just the path you want.
So if the folder is called C:\My Files\, you would add a step where Folder Path = C:\My Files\. This would filter out any subfolder.
 
I have no idea if it is a new feature, but if you use Folder.Contents, instead of Folder.Files, then only files of the specified folder are imported, without taking subfolders into account.
 
Back
Top