Parameter Tables

Overview

Parameter tables can be a very useful tool in the following types of situation:

Let’s say that for quite a few months you have been storing order data in a subfolder H:\Headers\Header Files. But now, you have to pass the solution to someone else to maintain. Their system however has the path mapped as J:\Headers\Header Files.

Rather than recode the solution for your replacement, and recode it all again when you return, you would really like to make the path relative to where the workbook is located. So, whether it is opened up from J:\Headers\Header Files or H:\Headers\Header Files or somewhere else, it should not make a difference.

Currently, there is no native function in the ‘M; language that allows you to work out the path to the workbook you are using, but Excel does have a formula that can do this.

To handle the above kind of situation, you would need to make use of some kind of parameter table. This articl explains how to do this:

Implementing Dynamic Parameter tables

Three main steps are involved:

1. Create a parameter table in Excel.
2. Create the function to extract the values from the table.
3. Modify your existing queries to call the function.

With this example, all file paths are hard coded to your PC, but we would like to deliver a solution that works irrespective of where the files are stored.

Steps

1. Create a Parameter table

• Create a table for holding parameters, like the one shown below:

For this table:

• The first column’s header is: ‘Parameter’
• The second column’s header is: ‘Value’
• The table has a name of: Parameters

The table can be used to hold other parameters (as many as desired).

• Next, give the first parameter, the name ‘FilePath’

In the ‘Value’ column, enter the following formula:

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

If you need to extend the file path, to any Sub Folders (where the source order files are kept), do so, as follows by using the & symbol, followed by the Folder Name, and a backslash:

    =LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1),1)-1)&“Varying Header Files\”

2. Implement a fnGetParameter function

Power Query now needs to be given a method to read the values, from the Parameter table.

This can be done with 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

This function connects to the Parameters table in the workbook, and then selects the row of the Excel table that matches Parameter name argument given to the function.

When a match is found, it then returns the value of the parameter, from the ‘Value’ column.

To create the function, take the following steps:

• Create a new query ➔ From Other Sources ➔ From Blank Query
• Go to Home ➔ Advanced Editor
• Highlight all rows of code in the window
• Copy the code above, and press Ctrl+V to replace what is in the Advanced Editor
• Click Done
• Change the function’s name to fnGetParameter

3. Calling the fnGetParameter Function

The last step is to modify the existing query used to source the correct file.

As the file path should update when the workbook is recalculated, it should always look for the files in the subdirectory of where the solution resides.

To modify the original query, click the little arrow on the left, next to the word Queries, to expand the Navigator window:

Now:

• Right-click the ‘Varying Header Files’ query → Advanced Editor
  (The Query was created in this article…Custom Functions - Varying Header Files)
• Insert the following line of code immediately after the let line, and modify the ‘Source’ step:

   fullfolder = fnGetParameter("FilePath"),

The query M-Code should now look like this:

let
    fullfolderpath = fnGetParameter("FilePath"),
    Source = Folder.Files(fullfolderpath),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each fnGetVaryingRows([Folder Path]&[Name])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name", "Folder Path"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID Number", "Amount", "OrderDate", "PreviousOrder", "DaysSinceLastOrder"}, {"ID Number", "Amount", "OrderDate", "PreviousOrder", "DaysSinceLastOrder"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"ID Number", Int64.Type}, {"Amount", Int64.Type}, {"OrderDate", type date}, {"PreviousOrder", type date}, {"DaysSinceLastOrder", Int64.Type}})
in
#"Changed Type"

When you have made the modifications, click Done, and the query should load as follows:

4. A final word

Referencing a parameter table and using custom functions gives a lot of flexibility. Whether you need to share documents with team members, or with other divisions of your company, you can create ETLs to read from dynamic folder structures, relative to your solution path – which is an incredibly useful feature!