Power Query - Using Parameters - Excel Off The Grid

As Power Query records the transformation steps, it includes many hardcoded values within the M code. For example, if we filter a column to select all values greater than 50, the 50 will be a hardcoded value in the M code. Or if we import a CSV file, the file path is hardcoded into the query.

What if we want to change these values or file paths? Obviously, we could edit the query each time, which would be very time-consuming. Or, we could create parameters.

The term “Parameter” may be a bit confusing. We use the term variable when writing VBA, or maybe the terms conditions, criteria or arguments when writing Excel formulas, these are all effectively the same thing. Parameters, variables, conditions, criteria and arguments are all values we can change to get a different output. Microsoft decided to use the term Parameter when designing Power Query.

In this post, we will be using cells values as parameters; therefore, by changing the cell value we can change the result of a query. The parameter could be contained within a CSV file, or as a database setting. But we’ll focus on the situation you’re most likely to encounter, which is a cell value.

As Power Query develops, new and easier to use features are introduced. Parameters are one of those developing features. The method I want to show you I believe is currently the easiest and best to implement.

Download the example files

It may be tempting just to read the text below, but you will retain the information much better if you work along with the examples.

Subscribers can download the files from the Downloads section.

All the examples in this post use Example 8 – Using Parameters.xlsx from the downloads.

The Excel workbook contains the source data only. We will work through all the steps from start to finish. Open the up the file and let’s get going.

Create the query

The first step is to create a query as normal. As noted, above, all the steps will be hardcoded.

Select any cell in the source table and click Data -> From Table/Range from the ribbon.

The Power Query editor will open. Make the following transformations

Date column

Click on the Date and Time icon next to the Date header, select Date from the menu.

Select the Date column header, then click Transform -> Date -> Month -> End Of Month

Filter the Date column to only include 31 January 2019. The date format may appear different depending on your location settings.

Ensure the Date column is still selected, then click Home -> Remove Columns

Sold By column

On the Sold By column, click on the filter icon and ensure only David is selected.

With the Sold By column still selected, click Home -> Remove Columns

That’s enough transformations for now. Click Home -> Close and Load.

The Table should look like this:

From the source data we created a Table which shows the products sold by David in January 2019. But what if we want the products sold by Sally in March 2019, or Mark in March 2019? This is where the parameters come in. In the next section, we will create some parameters to dynamically change the name and date.

Create the parameters

As a simple explanation, a parameter is just a normal query, in which we drill down into the value itself and load as a connection.

In this example, we will be using an Excel Table as the source, but it could equally be in named range, CSV, or any other data source we can get into Power Query.

On the worksheet which contains the query output table, create two tables with single values in them:

After creating each Table, I have renamed them.

The first Table I have named Date, and the second SoldBy.

Creating a Text parameter

First, we will create the parameter to change the name.

Select the cell in the SoldBy table and create a query, by clicking Data -> From Table/Range.

Pay close attention to the data type. The Sold By column in the original query above is a text data type, and the data type in this query is also text. We need these to be identical.

Within Power Query right-click on the value and select Drill Down from the menu.

The window will change to a view we have not seen before, the Text Tools window:

Make a note of the query name, which is SoldBy (no space), as shown in the screenshot above.

Click File -> Close and Load To…

From the Import Data window select Only Create Connection, then click OK.

The Queries & Connection menu will now show two queries, the original data, called Sales Data, and the text parameter called SoldBy.

Creating the Date parameter

OK, let’s go through the same steps again, for the Date parameter. Compared to the text parameter we created above, there is one additional step we need to make. In the original query, the Date column had a date type at the point it was filtered, therefore we need a date type for the parameter too.

After changing the Date column to a date type, right-click on the value and click Drill Down. Rather than Text Tools, it will be the DateTime Tools view.

Make a note of the query name, which is Date in this scenario.

As before, click Close & Load To… then select Create Connection Only and click OK.

We should now have two parameters created, SoldBy as a text type and Date as a date type.

Insert the parameters into the query

Having created the parameters, let’s use them. To do this, we are going to make some basic changes to the M code. We could use the Advanced Editor, or the Formula Bar. To keep things simple, I’ll use the Formula Bar for this example.

Important information: M code is case sensitive (SoldBy and soldby are not the same).

Open the original query (the SalesData query in our example).

If the Formula Bar is not visible, click View -> Formula Bar.

Find the step where we hardcoded the value David.

Replace “David” for the parameter SoldBy.

= Table.SelectRows(#"Removed Columns", each ([Sold By] = "David"))

becomes:

= Table.SelectRows(#"Removed Columns", each ([Sold By] = SoldBy))

Next, we will apply the Date parameter. Find the step where we hardcoded 31 January 2019 as the date.

Replace #date(2019, 1, 31) for the parameter Date.

= Table.SelectRows(#"Changed Type1", each ([Date] = #date(2019, 1, 31)))

becomes

= Table.SelectRows(#"Changed Type1", each ([Date] = Date))

That’s all there is, we have now applied the parameters. Click Home -> Close & Load to load the changes into Excel.

Using the parameter

Now that we are back in Excel, we can change the Date and Sold By cell values, then click Data -> Refresh All.

Wow! Magic eh? The query updates to show only the values for the parameters we have selected.

You can now set-up any Power Query hardcoded value as a parameter. I find the most useful things to set-up as parameters are:

File paths to import external data files
Period end dates for financial reports
Names of business divisions or cost centers to create reports for specific areas only
Any settings another user is likely to need to change

Power Query – Using Parameters