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 Series Contents
- Introduction
- Import Data
- Data Refresh
- Edit Queries
- Close & Load Options
- Using Parameters
- Basic Transformations
- Combine / Append Queries
- Import All Files in a Folder
- List All the Files in a Folder and File Attributes
- Import Data from the Current Workbook
- Import Data from the Web
- Unpivot Data
- Unstacking Data in a Column
- Lookup Values using Merge
- Change the Source Data Location
- Formulas
- If Statements for Conditional Logic
- Grouping and Summarizing Data
- Custom Functions
- Common Errors & How to Fix Them
- Tips and Tricks
Don’t forget:
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Great post! Exactly what I needed to solve and important issue at work! 🙂
Thank you!
Thanks – I’m glad it helped.
If I want to see Mark David and Sally’s sales together, this doesn’t seem to work by adding them to the parameter (table) SoldBy and refeshing all
Also if you use the table to filter out one of them – the table only returns to topmost in the list even if it has been deselected.
Hi Simon – Parameters only work with single values.
For your scenario using the Merge feature is a good option: https://exceloffthegrid.com/power-query-lookup-values-using-merge/
By using an Inner Join you can include the items which exist in both the data table and the lookup table.
HI. In recent work, adding parameters after a number of transformations resulted in the query breaking (parameters used to point to a file path and folder). What is the best point in query development to add parameters? I am thinking quite early on, say at the stage before major transformations are done (e.g., load the identified workbook ‘raw’; add parameters; continue query development). Any thoughts?
It shouldn’t matter when a parameter is added. I normally add parameters at the end once the query is working how I want.
When you say “query breaking” what do you mean? What was the error? Which build version of Excel are you using, as Power Query has been a bit flakey in some versions and would crash occasionally.
Many thanks for this clear explanation.
Thanks for your time.
Thank you. Parameters can be a little tricky.
Thank you so much for the brilliant explanation. I had been trying to no avail to understand parameters. This has really helped. Wish you could also give an example for when we can use a file path as a parameter.
The principles are the same for file paths, as for any other string parameter.
If you’re looking for a specific example, check out this post by Wyn Hopkins, which uses a file path parameter with a named range:
https://accessanalytic.com.au/powerquery_namedcells_parameters/
Thanks for your brilliant article.
What is the purpose of Drill Down here?
A parameter must be a value as a string, number, date etc. If you don’t drill down it won’t be a value, it will be a table.
Thanks for the easy to understand explanation. One question, how do you set the query to refresh automatically when a value changes.
Hey Reed – that’s a good question. I’ve just published a new post, which will answer this question:
https://exceloffthegrid.com/auto-refresh-power-query/
Hi
To me the specific reference by row number is quite inflexible, say I have more parameters, and in time want to add or remove one or more. Here I created a reference by keyword. For this just one extra step is required in the M query.
Also in the parameter table each parameter value needs to have a key value on the same row.
In your M query’s now you can use the key values as parameter instead of a row number.
Grtz
=====
Here is the code
let Parameter=(TableName,Key) =>
/*
call: fParameters(“Parameter”,”Keyvalue”)
“Parameter” is the table name that holds the key values and parameter values
“Keyvalue” is the value to search for, that references to the required parameter value
[Value] is the table column name that holds the parameter values
*/
let
Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
Search = Table.FindText(Source, Key),
value = Search[Value]{0}
in
value
in Parameter
==#==