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:

  1. 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.
  2. 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.

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!

Share:

Facebook
Twitter
LinkedIn

Related Posts

SCD2 Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

Calendar Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

Click Query Monkeys -> Measure Table Monkey to add measure table

Add Measure Table

Article Overview Many modelers like to store their measures on a single “Measures” table in the data model.  If you’re one of them, you may

Table Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

SmartFolder Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get