Power Query – Using Parameters

Power Query - Using Parameters

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 file

I recommend you download the example file for this post.  Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download Icon
Download the file: Power Query – Example Files

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.

Data From Table Range

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.

Change Date Time to Date

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

Change Date to Month End Date

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

Select 31 January Date

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

Home Remove Column

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:

Close and Load - Excel Table

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:

Create Parameter Tables on Worksheet

After creating each Table, I have renamed them.

Name a Table

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.

So, click on the icon next to the Sold By header, select Text from the menu.

Then, within Power Query right-click on the value and select Drill Down from the menu.

Right-click drill down

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

Text Tools Window Power Query

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

Click File -> Close and Load To…

File Close and Load To

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

Import Data - Connection Only

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

Queries & Connection Table and Parameter

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.

So, click on the Date and Time icon next to the Date header, select Date from the menu

Next, right-click on the value and click Drill Down.  Rather than Text Tools, it will be the DateTime Tools view.

DateTime Tools Window Power Query

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.

Queries & Connection Table and Two Parameters

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.

Power Query - Change Text Parameter

Replace “David” for the parameter SoldBy.

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


= 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.

Apply Date Parameter

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

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


= 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.

Change cell values - parameters

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

Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. 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:

25 thoughts on “Power Query – Using Parameters

  1. Simon says:

    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.

  2. Nigel Haskins says:

    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?

    • Excel Off The Grid says:

      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.

  3. Gift Phoya says:

    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.

    • Excel Off The Grid says:

      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.

  4. Reed May says:

    Thanks for the easy to understand explanation. One question, how do you set the query to refresh automatically when a value changes.

  5. Robert says:

    I After I change the hardcoded parameter in my query, I got “Unknown colum ‘IDRange’ in ‘where clause’. IDRange is the table name I give to the parameter table.

  6. Goof says:


    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.
    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
    Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
    Search = Table.FindText(Source, Key),
    value = Search[Value]{0}

    in Parameter

  7. Alan says:

    Hi thank you this is really useful.

    I have one question: is it possible to not filter even when you have a parameter? I mean sometime I want to use the parameter, sometimes I just want to have all values. Thanks in advance!

  8. Kavin says:

    Would you know what’s the query parameter would be, or what should I set the parameter value to be, if I want to include all entries (or not blank)? Thanks in advance

  9. Alicia Diaz says:

    This was very helpful.

    I noticed with the setup of the Date parameter that you mention an extra step but don’t describe or show it in detail – it might be obvious to other folks, but it took me a while to troubleshoot why the table was suddenly blank. I finally figured out what I needed to do to update the date format in the new Date query; it might be helpful to the next person to add “Click on the Date and Time icon next to the Date header, select Date from the menu.” in the section on building the Date parameter.

    Thank you again for posting this – it is a fabulous example!

    • Excel Off The Grid says:

      Hi Alicia – Thank you for the feedback. Because this is part of a wider series on Power Query, I don’t explain all the steps in some of the later posts.

      But, I don’t want others to be confused, so I have added the text as you’re recommended, as I think that will help. 😀

Leave a Reply

Your email address will not be published. Required fields are marked *