Monkey Tools

Smart Folder Function

Article Overview

If you save your files into folders that sync to OneDrive for Business or SharePoint, you’ll know the pain that happens if you try to reference a folder based on the path returned by the CELL() function… The SmartFolder function provides the answer to that problem, as you can pass it either a local path on your hard drive or the web path and it will “smart switch” to use the correct Power Query connector to read from the local or SharePoint folder.

In this article, we’ll explore exactly what this Monkey does, as well as how to use it.

About the SmartFolder Function

What this Monkey does

This feature inserts the SmartFolder function into the workbook, allowing you to solve a couple of issues…

The ultimate goal is to provide a list of files in the target folder, with a little future-proofing done in the form of forcing all file extensions to lower case. (This ensures that filtering for an .xlsx file will never miss .XLSX files.) In addition, it also removes the target folder from the Folder name column, making it very easy to see what sub folders (if any) still exist in the list.

On its own these things seem relatively minor, but they are just a convenience for the function’s larger goals…

The 'true' purpose of the SmartFolder function

The real benefit of this function is that it can accept a local drive path to a folder on your system, or the full path to a subfolder on an Office 365 SharePoint site. This makes connecting to a SharePoint folder much easier, as it saves you having to work out the root of the site, connect to it, and then filter the Folder name column to drill down to the files you actually want. The function does all of that for you and can switch easily should your file path “smart switch” from local to SharePoint based on Microsoft’s OneDrive policy implementation. Even better, it does this all without violating the Formula Firewall!

Implementing the SmartFolder function

Adding this function to your workbook is very easy.

Go to the Monkey Tools ribbon tab
Click Query Monkeys -> SmartFolder Function

A new query called fnSmartFolder will be injected directly into your workbook and show up in the Queries & Connections pane, ready for your use.

Using the fnSmartFolder function

To invoke the fnSmartFolder function you have multiple different options, depending on your needs. To get started, double click the fnSmartFolder function in the Queries & Connections pane. You will be launched into the Power Query editor, where you will be greeted with full documentation on how to use the function. Feel free to read all of it if you like, or just jump ahead and configure the parameters as outlined below

Option 1: List all Files in the Folder and Subfolders

To list all files in the selected folder and subfolders, all you need to do is scroll down the the Parameters section and:

Put your full folder path into the “FullFolderPath” field
Optional: type true in the “IncludeSubfolders” field

Upon clicking the Invoke button, you’ll a new query will be created which shows all files in the folder and subfolders.

There are four important things to notice about the output from this function:

East.xlsx (on both rows 7 and 11) have an upper case XLSX as the file extension. fnSmartFolder automatically converts it to lower case for the Extension column to prevent you from missing it when you filter for “xlsx” files.
The Folder Path strips the original file path off, making it easier to filter to your subfolders. As an added bonus this future-proofs the solution by avoiding hard coding the entire file path in your filters, allowing the function to smart switch between SharePoint and Local paths without errors.
If the folder path is a url, the monkey will replace all forward slash (/) characters with the backslash (\) character. This allows you to set up filters into subfolders without worrying that they will break if the data source changes from local to web based or vice versa.
This setup follows Microsoft’s standard “Get Data -> From Folder” setup, reading all files from the targeted folder and any subfolders. This can be incredibly useful, but can also carry performance penalties when there is a large number of files in the folder hierarchy.

Don't forget... The path you provide can be a local file path, an Office 365 based SharePoint path, or even the path to a OneDrive for business folder!

Option 2: List all Files in the Target Folder Only

When you have hundreds (or thousands) of files in your subfolder hierarchy, the above method can take ages to refresh – even if you are filtering to a specific folder. The reason for this is that Power Query still has to enumerate all files in order to determine if they should be included in your subset or not. WIth a simple switch of the second parameter in this function, we can easily target just the folder, making our refreshes much more efficient.

To list only the files in the targeted folder:

Put your full folder path into the “FullFolderPath” field
Type false in the “IncludeSubfolders” field

Upon clicking the Invoke button, you’ll a new query will be created which shows the contents of the target folder only:

This result also has some notable components:

The target folder used in the illustration is the identical folder shown in Option 1 above, but returns very different results.
Notice that the CSVs and Production Forecasts folders show as Tables (which you can drill into), but none of their files show in this listing. Should you want those files, you will need to drill into the appropriate subfolder.
This listing enumerates 6 items total (4 files and 2 folders) instead of the (potentially) thousands of files in the subfolder hierarchy shown in Option 1, making it much faster.

Consider this... You can build multiple invocations which target different subfolders directly, then append them. Depending on the count on unneeded files in your subfolder hierarchy, this could yield significant time savings over the default "From Folder" methods.

Feeding the function a dynamic file path

To feed this function a dynamic file path, first insert a Parameter Table/Query via the Get Parameter and Query Monkey. Then create a query that passes the file path into the function by using the following steps:

Make sure your workbook has been saved
If you don’t already have it, insert a Parameter Table/Query
Insert the SmartFolder query in your workbook
Click the Blank Query button on the Monkey Tools ribbon
Type in the following formula in the formula bar and press Enter:

				
					=fnSmartFolder( fnGetParameter("File Path") )

At this point you’ve got the list of all the files from the folder in which you’ve stored the workbook and can filer to subfolders from that location. And the best part? Whether the CELL() function enumerates the local path or SharePoint-hosted path, it will still work!

Pro Tip! If you'd prefer to let our Monkeys do most of the work for you, you should really check out the SmartFolder Monkey. Not only does it inject the fnSmartFolder function, it also takes care of the Parameter Table, fnGetParameter function, and hooks them all together so that you don't have to!

Monkey Tools

Smart Folder Function

Article Overview

About the SmartFolder Function

What this Monkey does

The 'true' purpose of the SmartFolder function

Implementing the SmartFolder function

Using the fnSmartFolder function

Option 1: List all Files in the Folder and Subfolders

Option 2: List all Files in the Target Folder Only

Feeding the function a dynamic file path

Share:

Related Posts

SCD2 Monkey

Calendar Monkey

Add Measure Table

Table Monkey

SmartFolder Monkey