When it creates the code, elements of it will be hardcoded. For example if you’re importing data from an external source the folder path and file name will both be a hardcoded (static or unchanging) string of text in the M code. If you want to update the folder path or file name to be imported then you need to go into the advanced editor and update the path and file name. To avoid this, I like to set up a parameter table in my workbooks. This allows me to easily update folders, file names and other inputs in my queries. In this example we’re going to import some data in a CSV and do some minor transformations. Then we will parameterize the resulting query so we can easily update it.
Video Tutorial
Set Up A Parameter Table
Let us set up our parameter table. For this query, I want to import a CSV from a folder and then filter on a given product and date range. I want to have input parameters so I can easily update the folder path, file name, product, and date range that my query will be based on. The table I created has 3 columns, but it only really needs the Value column where the input value is. The Index and Parameter column are for information and are just there to remind me what row number a value is in (the Index) and a description of what the value is used for (the Parameter). We need to turn the parameter data into an Excel table by going to the Insert tab and selecting Table or by using the Ctrl + T keyboard shortcut. Name the table Parameters, this is how we will reference the table in our power query. To name a table select it and go to the Design tab and type in a new name under the Table Name: box.
Create A Query Function To Reference Your Parameter Table
Create a blank query. Go to the Data tab in the ribbon and select Get Data in the Get & Transform Data section. Select From Other Sources then select Blank Query from the menu.
Name the query fParameters. This will be how you call the values in your parameter table.
Open the Advanced Editor from either the Home tab or the View tab in the query editor. Copy and paste in the following code then press the Done button. This query function has two inputs TableName and RowNumber.
TableName – This input refers to the table name. In our case it will be “Parameters“.RowNumber – This input refers to the row number that our parameter of interest is in within our table.
Note that power query will count rows from 0, so we will use RowNumber-1 in order to reference our rows in the natural counting order from 1 to N.
Now save the query function.
Creating Our Initial Import And Filter Query
Go to the Data tab in the ribbon and select From Text/CSV in the Get & Transform Data section. Select your CSV file then press the Edit button in your query result preview window.
Now let’s add a filter.
Set the filter to Pencils and press the OK button.
Now we can do a similar thing to filter the Order Date between 2017-02-01 and 2017-03-31. If you open up the Advanced Editor from the Home tab, your query should look like the following. You can see the parts that are hardcoded (highlighted in red). We can Close & Load this query to a Table in a new sheet and see our data.
Replace The Hardcoded Items With Our Query Function
Now we can replace any instance of a hardcoded reference we wish to turn into a parameter in our query. Replace the text including any parathesis around them with fParameters(“Parameters”,N) where N is the Index in our parameter table which we want to refer to. For the dates, I’ve had to use the Date.Year, Date.Month and Date.Day power query function to convert my parameter dates into numbers for the year, month and day.
Now we can easily change any of the parameters and Refresh the query! We can create much more flexible queries with this parameterization method.
