Chapter 23 Dynamic Parameter Tables

In Chapter 21 we explored how custom functions can be used to preprocess data sets prior to merging. While this is a fantastic benefit of custom functions, it is far from the only one.

Consider the example in the last chapter, of pulling timesheets from a folder. Say that you’ve built the master consolidation file, saved it in H:\Payroll, and for months have been storing the timesheets in the subfolder H:\Payroll\Timesheets. After working hard all year, you finally get a few weeks off and have to pass the solution to someone else to maintain while you’re gone. There’s a problem, though: Their system has the path to your solution mapped as J:\HR\Payroll. Rather than recode the solution for your replacement and then have to recode it again when you return, you’d really like to make the path relative to where the workbook is located. That way, if the user opens it up from J:\HR\Payroll or H:\Payroll or something else, it shouldn’t make a difference.

The real challenge? There is currently no function in the M language that allows you to work out the path to the workbook you’re using. Interestingly, an Excel formula can do this work.

Warning: Before we embark on the journey of making the file path dynamic, there is something you should know about this technique: Queries where data access happens inside a function and where the data source is dependent on parameters to the function can’t currently be refreshed in Power BI. This is because Microsoft performs static analysis of the query to discover the data source, and the static analysis can’t yet handle this scenario.

This does not mean that the functionality is not useful, and it doesn’t mean that it won’t be supported one day. It simply means that you should rely on it only if the solution is going to live in Desktop Excel and not on the web.

Implementing Dynamic Parameter Tables

There are three steps to implementing parameter tables into your solution:

1. Create a parameter table in Excel.

2. Create the function to extract the values from the table.

3. Retrofit your existing queries to call the function.

We will walk through each step of this process using the Ch 23 Examples\Parameter Tables.xlsx file.

Upon opening this file, you’ll recognize that it is a continuation of the Timesheets file that you’ve built over a couple of chapters. The challenge with this file is that currently the paths are all hard coded to your PC, (the author’s in the case of the example file,) and now you’d like to deliver the solution so that it works no matter where you store it.

Step 1: Creating a Parameter Table

The first thing you need to do is create a table to hold your parameters. This table should take a specific form, and it needs to have certain components set up correctly.

Create the table shown below in cell A7:B8 of the Info worksheet:

Figure 302 The barebones Parameters table.

Notice several key characteristics of this table:

Warning: Each of these characteristics must be correct if you want to copy and paste a function you’ve been provided in the download files. If even one of these items is spelled differently, you will need to debug the table or the function.

The table is now set up to hold every piece of data that you want to use as a dynamic variable in your solution. Simply provide the name of the parameter on the left and the value for the parameter in the Values column.

Note: The items in the Values column can be hard coded text or values, they can be driven by data validation lists, or they can use formulas. How you get the correct value in the cell is completely up to you as the solution designer.

Next, you need to determine the file path to the current workbook. Enter the formula below into cell B8:

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

Warning: If you haven’t saved your file, this function won’t return the path as it can’t determine where the workbook lives. Saving the file will fix this issue for you.

Upon doing so, you should see the file path to the workbook listed. The only challenge is that you actually want this file path to point to the Source Files folder, which is where the timesheets are stored. Update the formula as below:

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&"Source Files\"

Figure 303 Dynamically returning the file path using an Excel formula.

Step 2: Implementing the fnGetParameter Function

With the parameter table now in a state that can hold any variable you need, you need to give Power Query a method to read those values. This portion can be done by using the following custom function:

(ParameterName as text) =>

let

ParamSource = Excel.CurrentWorkbook()

{[Name="Parameters"]}[Content],

ParamRow = Table.SelectRows(ParamSource,

each ([Parameter]=ParameterName)),

Value=

if Table.IsEmpty(ParamRow)=true

then null

else Record.Field(ParamRow{0},"Value")

in

Value

Note: This function is contained in the fnGetParameter.txt file in the Ch23 Examples folder. In addition to the code, it also contains instructions to use the function, as well as the formula to return the file path from a cell. The file is provided to give you a template you can store and use multiple times.

This code connects to the Parameters table in the workbook and then selects the row of the table where the dynamic parameter’s value matches the record in the Parameter column of the Excel table. With that match in place, it then returns what it finds in the Value column. It is because each of these names is hard coded in the function that both the table and column names for the Excel table match what was specified above.

Rather than retype this entire block of code, open the Ch23 Examples\fnGetParameter.txt file and copy all lines inside the file. With those in the paste buffer, it will be deadly simple to implement this function into your solution:

And you’re done.

Step 3: Calling the fnGetParameter Function

With the parameter table built, and the function in place, the last step is to retrofit the existing query to actually use it. Doing so will allow you to source the file path from the cell and use that in your query. Because the file path updates when the workbook is recalculated, it will always be accurate, which means the solution will always look for the timesheet files in the subdirectory of where the solution resides.

To retrofit the Timesheets query, you don’t even have to leave the Power Query editor. You can just click the little arrow on the left next to the word Queries to expand the Navigator window:

Figure 304 Expanding the Navigator window.

The Navigator allows you to select any of your queries or functions, so you can very quickly flip back and forth, making changes and testing the effects that they have on other queries. Here’s what you do now:

The query should now look like this:

Figure 305 Calling the fnGetParameter function.

You have created a new variable called fullfilepath to hold the value from the File Path row of the Excel table.

It is also worth noting here that you are not strictly required to create a new variable in order to use our function. You could skip this step and just nest the fnGetParameter call in place of the file path on the next row. By adding this call on a separate line, however, you make the query much easier to debug, as you’ll see now:

The full file path to the folder is displayed nicely in the editor, giving you the comfort that you have that part correct:

Figure 306 The fullfilepath variable is correctly pulling the file path.

Now that you know that the function is returning the correct path via the Excel formula, you can slipstream the variable in place of the hard-coded file path in the Source step:

The first three lines of the query should now read as follows:

let

fullfilepath = fnGetParameter("File Path"),

Source = Folder.Files(fullfilepath),

Note: You must edit the M code manually to make this work. It can’t be accomplished by clicking the gear icon next to the Source step, as fullfilepath is not a valid folder to the Windows operating system.

When you’ve made the modifications, you can click Done, and you’ll see that every step of the query still functions correctly.

Figure 307 The retrofitted query still works.

Implications of Parameter Tables

Referencing a parameter table gives us a huge amount of flexibility when building solutions. Whether you are building solutions internally in your company and need to share them with team members or other divisions, you can now set them up to read from dynamic folder structures relative to your solution path. If you develop solutions for clients, this is also hugely impactful, as it is doubtful that you’ll ever have exactly the same file structure on your system as your client does. The last thing you want to do in either of these situations is send the end user a file with instructions on how to edit the M code.

But the power of parameter tables doesn’t end there. Consider each of the following tasks that you may wish to perform:

By setting up and using a custom function to read from an Excel table, we can accomplish any of these goals. This affords us not only the ability to dynamically drive our content, but also gives us the ability to generate data in an environment more familiar to us, and in some cases do things that Power Query wouldn’t otherwise allow us to do.

Again, we need to call out the caveat that if you are publishing to Power BI, this solution won’t work for you, as Power BI doesn’t currently support dynamically generating paths to databases and the like. If you are developing your content in an Excel desktop environment, however, you’ll find this to be one of the most useful techniques in this book.