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:

A new “Query Options” worksheet
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

NOTE: All new parameters must be added in the row immediately below the last row of the table, as the table expands automatically to include them. You may use hard coded values, text, or formulas in the Value column of the table, making this solution fairly dynamic.

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:

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

Don't forget! You need to place a comma at the end of the line you use to create your variable!

Working with the File Path Parameter

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

The cell returns “Workbook needs to be saved!”

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.

The cell returns a URL instead of a local 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”.

Monkey Tools

Parameter Table and Function

Article Overview

Working with the Parameter Table and Function Monkey

What this Monkey does

The Query Options worksheet

The fnGetParameter Function

Working with the File Path Parameter

Share:

Related Posts

SCD2 Monkey

Calendar Monkey

Add Measure Table

Table Monkey

SmartFolder Monkey