Monkey Tools

Parameter Table and Function

Article Overview

If you’re a fan of the fnGetParameter function (as per Chapter 17 of my Master Your Data book), you’ll love this Monkey. With just a click, it will insert the Parameter Table and fnGetParameter function into your workbook.  It even places the Excel File Path formula in the cell for you!

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

Working with the Parameter Table and Function Monkey

What this Monkey does

The purpose of this button is to quickly inject the key components into your workbook to allow you to feed your query parameters that are maintained in the Excel worksheet. Upon selecting this button, two things are added to the workbook:

  1. A new “Query Options” worksheet
  2. The “fnGetParameter” function
Let’s explore each of these now…

The Query Options worksheet

This worksheet is injected with the table required to feed the fnGetParameter function. It comes pre-loaded with a default variable of “File Path” and the required Excel formula to generate it.

To add new parameters to your table, simply:

  • Type in the new parameter name in column B
  • Type the value for the parameter in column C

The fnGetParameter Function

The second part that gets injected into your file via the Parameter Table & Function feature is the fnGetParameter function.

This function allows you to pull parameters from the Excel table, and use them in your other queries by typing the following in the Power Query editor’s formula bar:

					=fnGetParameter("<the parameter name>")

Just replace your <the parameter name> with the exact name that you used in the first column of the parameter table.

In order to reduce your chances of falling afoul of Power Query’s Formula Firewall, we highly recommend that you never nest this function call directly into another query step. Instead, edit your existing query in the Advanced Editor and – immediately after the let line – create a variable to contain the result of the fnGetParameter function. Then, refer to that variable with the item you wish to make dynamic.

In the code below, we have created a variable called filepath, assigned it the value from the “File Path” cell in the Excel table, and then fed that into the Folder.Files() function:

	filepath = fnGetParameter("File Path"),
	Source = Folder.Files(filepath)

Working with the File Path Parameter

There are two potential issues that you will experience when working with the File Path parameter specifically:

This message indicates that you have injected the queries into a new file, and it doesn’t yet have a save location. Save the workbook, select the cell, press F2 and then Enter, and it will update to the new file path.

This issue occurs for files saved in a OneDrive sync folder when the user has the Office 365 version of Excel installed. Unfortunately, Microsoft has not provided us a way to determine the local file path, so the only way to ensure you get the local file path is to edit your OneDrive sync settings and uncheck the box shown here:

For more about this query, read the Excelguru blog post on the subject. The query is virtually identical to what has been shared there, with the exception that it reads from a table called “XLG_Parameters” so as not to conflict with any existing tables in the workbook using the name “Parameters”.



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

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