Monkey Tools

SmartFolder Monkey

Article Overview

One of our favourite tricks is to create a “From Folder” solution which avoids hard-coding a file path, instead pulling it dynamically via our fnGetParameter setup. The issue is that this creates a link to either a local or SharePoint / OneDrive for Business folder. However, this causes problems when you open the file in a state that returns the alternate path, as Power Query user different connectors.

The SmartFolder Monkey solves all of this for you. It creates an easy to use interface to inject the fnGetParameter and fnSmartFolder queries, links them together, and gets you up and running fast with a solution that avoids triggering the formula firewall error.

Working With the SmartFolder Monkey

What this Monkey Does

Overall, this Monkey does a few things for you. It will:

  1. Inject the fnGetParameter table and function into your workbook (if it doesn’t already exist).
  2. Inject the fnSmartFolder function into your workbook (again, if it is not present) or offer to update it (if you are using an older version).
  3. Prompt you for a new name for your listing of files.
  4. Ask if you’d like to list all files in subfolders or the target folder only.
  5. Create the “FilesList” query by combining the fnGetParameter and fnGetSmartFolder queries properly.
  6. Convert all slashes (/) and backslashes (\) to a consistent character.
  7. Trim out the “root folder” from the Folder Path column.
  8. Convert all extensions to lower case (for each filtering).

All you then need to do is set the Privacy for the query and provided any SharePoint credentials in order to access the solution. Once done, you’ll never again be prompted with errors due to a file path change!

Initial Setup for the SmartFolder Monkey

The SmartFolder Monkey is intended to get you up and running with a dynamic From Folder solution quickly. To do so, our recommended steps are:

  1. SAVE YOUR FILE. (This is important to make sure that your file has a valid file path.)
  2. Go to Query Monkeys -> SmartFolder Monkey.
  3. Choose a name for your SmartFolder query (the Monkey will default to calling it “FilesList” as you get a list of files), the appropriate parameter from your Parameter table and whether you would like to list all files in Subfolders (or the target folder only)

If Excel recognizes your file as stored in a SharePoint or OneDrive for Business folder, you will then need to do the following:

  1. Edit the “FilesList” query.
  2. Declare your Privacy as “Organizational” and log in if necessary.
  3. Close the Query Editor and save the file to commit the changes.
At this point you will now have a FilesList query which gracefully handles a change in file path without causing any errors.
The SmartFolder function lives on the Monkey Tools -> Query Sleuths menu
Step 2: Select the SmartFolder Monkey from the Query Monkeys menu
A message box prompting for the name of the FilesList query to be created
Step 3: Choose a name for your FilesList query
The Queries & Connections pane showing the fnGetParameter, fnSmartFolder functions and the FilesList query which shows an error indicator
Step 4: Double click the FilesList query to edit it
The error message showing that privacy levels need to be declared, as well as how to set them to Organizational
Step 5: Set the Privacy Level of the workbook to Organizational
The error showing that the user is not logged in, followed by logging into a "Microsoft Account" for SharePoint and OneDrive for Business sources
Step 6: Log in to the SharePoint site if required
A Power Query files listing
The results of the SmartFolder function returning a files listing for all files in the workbook's home folder and subfolders

Recommended Next Steps for the SmartFolder Monkey

The next component to know is how to set up your solution for success if you would like to use Power Query’s combine or merging functionalities. We highly recommend that you do not perform these operations directly in the FilesList query, but rather you take the following actions:

  1. Right click the “FilesList” query -> Reference.
  2. Rename the new query.
  3. (Optional) Load this query to a worksheet or the Data Model (or load as connection only to create a new “staging” query). 
Right clicking the FilesList query and choosing Reference to create a new query which points to the FilesList
Right click the FilesList query and choose Reference to create a new query that links to the FilesList query

Ideal Folder Setup for SmartFolder Monkey

The SmartFolder works by returning the path to where the current workbook is saved via Excel’s CELL() function. If you are planning to combine files from multiple folders, we recommend setting up a folder structure that lives within the folder where your workbook is saved. This offers the following benefits:

  1. You can zip the entire folder for backup/sharing purposes and it will contain the solution and the required data files.
  2. It makes it super easy to re-use the FilesList folder by referencing it, then filtering or drilling into the Folder Path column to the subfolder you need.

 

A folder listing showing the ideal location to save the solution (in the root folder)

The Impact of the "Show Files in Subfolders" Choice

Monkey Tools defaults are built to mimic the default “From Folder” behaviour provided by Microsoft.  What this means is that our monkeys provide you with a query that shows all files in the target folder, as well as subfolders.

For some solutions this can be very useful as – looking at the Financials image above, you would be able to easily filter to all folders which contain the term “Statements” and aggregate every file for all years.  The drawback of this setup, however, is that Power Query needs to filter out all irrelevant files first. Depending on how many tens, hundreds or thousands of files you have in the subfolder hierarchy, this could take a long time.

For this reason, SmartFolder Monkey makes it easy to choose a different connector which returns a list of only the files in the target folder.

Compare: Show files in subfolders

Let’s compare what the two options here look like.  To begin with, consider the following output, which is the result of targeting a specific subfolder with the “Show files in subfolders” option checked:

Notice that we have four files in the root of the folder, two in a subfolder called CSVs, and hundreds in the Production Forecast folder and subfolders.  If we just want the two csv files, a filter to the CSVs folder will work, but Power Query still needs to enumerate through the hundreds of files in the other folders to compare if their path is equal to the CSV folder.  Ouch!

Compare: Do not Show files in subfolders

Now let’s see what a specific subfolder with the “Show files in subfolders” option un-checked looks like:

We still have our four files in the target folder, but instead of all files in all subfolders, we now show two tables representing the subfolders of the target folder. The fact that there are hundreds of files in those subfolders is irrelevant to Power Query’s performance however, as it only needs to enumerate the six items living in this folder.  Should you want the CSVs, just click the Table keyword to drill into it and see those files.

See the SmartFolder Monkey in Action

Prefer to watch a live demonstration? Check out the recording of Ken’s presentation to the KSA Excel user group when it was first introduced as a new Monkey Tools feature!  (The Query Monkeys menu was a little shorter back then!)

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

Create Query from M Code

Article Overview One of the things that we do at Monkey Tools is help people with questions in forums. When someone provides us with an

Copyright Excelguru Consulting Inc.