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
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.
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 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!
Adding this function to your workbook is very easy.
A new query called fnSmartFolder will be injected directly into your workbook and show up in the Queries & Connections pane, ready for your use.
To invoke the fnSmartFolder function. you have (at least) two different options, depending on your needs.
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:
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:
=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!
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
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
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
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
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