“I have a SharePoint library with multiple parent folders, each with the same folder inside, how can I get all the files in these subfolders in a single Power Automate flow?”
The standard Power Automate action ‘Get files (properties only)’ allows you to limit the entries to a folder. But it’s just one folder. That’s fine in most situations, but what if you need to search in multiple folders? If your folder structure is more complex, e.g. multiple folders, each with their own ‘Draft’ subfolder? And you want all the documents from that subfolder?
The ‘Get files…’ action itself won’t help you in such situation. What you’ll need is to use a Filter Query.
Check the file path
One of the available file properties is the path to the file. If you used that in the Filter Query, you could search only for files that contain the specific folder in their path.
Unfortunately, you can’t use the ‘{FullPath}’ property in the Filter Query. But as already explained in the post on filtering by file name, you can use a similar path. The server relative path represented by the property with internal name ‘FileRef’.
Combine it with the substringof(…) filter in the Filter Query, and check if the path contains the specific folder, e.g.
substringof('Draft', FileRef)
Such ‘Get files…’ action will return only files with ‘Draft’ in their path = files in the ‘Draft’ folder.
Skip the folders themselves
Since the ‘Get files…’ will return everything with the folder name in the path, it’ll return the folder(s) too. Processing a folder could cause errors in your flow as it’s not an actual file. It doesn’t have content nor properties, and it’s better to ignore them. Do that by using the other “hidden” file property: FileLeafRef – the file name with extension. As the folder doesn’t have any extension, it’ll contain just the folder name.
The file path must contain ‘Draft’ (the folder name), but at the same time the file can’t be called ‘Draft’. Add the second filter to the Filter Query with the AND operator.
substringof('Draft', FileRef) and FileLeafRef ne 'Draft'
The result will be only the files in the ‘Draft’ folders.
Summary
Even if Power Automate doesn’t allow you to get files from multiple SharePoint folders directly, you can still filter them out with a Filter Query. A Filter Query that looks simple, but there’re some advanced concepts behind. There’s the substring(…) filter, where I often enter the parameters in the wrong order. The FileRef and FileLeafRef properties, not shown anywhere in the file properties, but necessary to work with files. And the combination of multiple filters in a single Filter Query.