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 (at least) two different options, depending on your needs.

Option 1: Manually invocation

Double-click the function in the Queries & Connections pane, and type in the file path you wish to read from. You’ll be prompted to enter the folder path as shown here:

Upon clicking OK, you’ll be taken into the Power Query editor and find yourself in a new query, ready to do what you want with the files.

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

Budget.CSV has an upper case CSV 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 “csv” 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.

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: Feed the function a dynamic file path

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:

If you don’t already have it, insert a Parameter Table/Query
Insert the SmartFolder query
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: Manually invocation

Option 2: Feed the function a dynamic file path

Share:

Related Posts

SCD2 Monkey

Calendar Monkey

Add Measure Table

Table Monkey

SmartFolder Monkey