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


Power Query Parameters: 3 methods + 1 simple example

Power Query - Using Parameters

As Power Query records 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 is a hardcoded value in the M code. For something more dynamic, that we can easily change, we use Power Query parameters.

Obviously, we could edit the query each time we want to use it. However, this would be very time-consuming (and become very annoying, very quickly). So instead, parameters are the best way to make our queries flexible for different outputs.

Watch the video


Watch the video on YouTube.

Download the example file

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

Download Icon

Download the file: 0103 Power Query Parameters.zip

The download contains the source data and the completed solution. In this post, we work through all the steps from start to finish to demonstrate how to build the complete solution. So, open up the 0103 Power Query Parameters.xlsx workbook, and let’s get going.

What is a Power Query parameter?

The term “Parameter” may seem a bit confusing. We use the term variable when writing VBA, or in Excel, we might use the terms conditions, criteria, or arguments. Effectively, these all describe the same thing. Parameters, variables, conditions, criteria, and arguments are all values we change to obtain a different output. Microsoft decided to use the term Parameter when designing Power Query.

There are many ways to create parameters. Initially, this post focuses on creating parameters from cell values. As Excel users, these will be the most useful for our needs. Later in the post, we consider other methods to give you a complete picture. Then you can choose the best approach for your scenario.

Technically, using values from cells cannot be called a parameter. This is because, in Power Query, parameters have a particular method of creation. However, for our purposes, cell values can operate like parameters; we will use them like parameters; so let’s just call them “unofficial” parameters. There is more on “official” parameters later in the post.

Scenario

In our example scenario, we want to filter the data for a specific date and employee name based on cell values. So, each time we refresh the data, Power Query reads the current cell values and applies them as parameters in the query.

This is the example data we are using:

Sample data snapshot

Create the query

The first step is to create a query as normal.

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

Data - Table Range

The Power Query editor opens with the data.

Apply transformations

Let’s make the following transformations

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

Change date data type

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

Change date to end of month

All values in the Date column are now month-end dates.

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

Filter date column

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

Remove date column

The transformations required for the Sold By column are similar to those above.

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. Name the query SalesData, then click Home > Close and Load to load the data into an Excel Table on the worksheet.

The Table should look like this,

Close and Load - Excel Table

From the source data, we created a table showing David’s products in January 2019. But what if we want the products sold by Sally in March 2019, or Mark in February 2019? This is where the parameters come in. 

In the next section, we create parameters to change the name and date dynamically.



Create the parameters

As noted above, initially, we are focusing on cell-based parameters. In these scenarios, a parameter is just a standard query in which we drill down into the value of a single cell. This parameter query is not loaded back into the worksheet; it is purely used as a placeholder in the M code of another query. As a result, all parameter queries are loaded as a connection only.

In this example, we are using an Excel Table as the source for parameters, but it could equally be a named range, CSV, SharePoint list, or any other data source we can get into Power Query.

On the worksheet that contains the query output table, create two tables with single values in each.

Table 1

  • Header: Date
  • Value: 31 March 2019

Table 2

  • Header: Sold By
  • Value: Sally

After creating each Table, rename them, so they have meaningful names. The first Table I named Date, and the second SoldBy.

Tables Renamed

Create the Text parameter

First, let’s create the parameter to hold the name.

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

We need to pay close attention to the data type. The Sold By column in the original SalesData query was a text data type. The data type for this query must also be text. If necessary, change the data type to text.

Next, 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 view.

Text Tools view

We don’t need the SoldBy query on our worksheet; therefore, we want to load it as a connection only. Click File > Close & Load To… (or Home > Close & Load (drop-down) > Close and Load To…).

Close and Load To

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

Only Create Connection

The Queries & Connection pane will now show two queries: the original query, SalesData, and the text parameter, SoldBy.

Queries and connections pane displaying two queries

Create the Date parameter

OK, let’s repeat the same steps for the Date parameter. 

As noted above, parameters need to have the correct data type. In the original SalesData query, the Date column had a date type. Therefore, we need the parameter to have a date type too.

Load the Date table into Power Query. Then, click on the icon next to the Date header and select Date from the menu. This changes the column to a date data type.

Next, right-click on the value and click Drill Down. Rather than Text Tools, as we saw for the first parameter, this time it will be the DateTime Tools view.

Date Time Tools View in Power Query

As before, click Close & Load To…

Then, in the Import Data dialog box, 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 & connections pane with two connection queries

Insert the parameters into the query

We have created the parameters; we are now ready to use them. To do this, we will make simple M code changes. We could use the Advanced Editor or the Formula Bar for this. To keep things simple, we’ll use the Formula Bar.

Important: Be careful of your typing because M code is case-sensitive.

Open the original SalesData query.

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

Find the step where we hard-coded the value, David.

PQ step to filter to name

Replace “David” for the parameter SoldBy.

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

Becomes:

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

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

PQ Step to filter by 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 applied the parameters. 

Click Home > Close & Load to load the changes into Excel.

Using the parameters

Once we are back in Excel, we can change the Date and Sold By table values, then click Data > Refresh All.

The query refreshes to show only the items we have requested.

Power Query Parameters used inside Excel

Wow! Magic, eh!

We can use this method to 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 (check out this post for more details: Change Power Query source based on cell value)
  • 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

These are the type of scenarios we are most likely to face as Excel users.

Other types of parameters

Technically, what we have seen so far are not “parameters”. Parameters inside Power Query have special features. We have used queries linked to cells to behave like parameters. But they do not contain the special features.

Manage parameters

Official parameters have features which Power Query uses for advanced functionality (e.g., Custom functions or incremental refresh inside Power BI). However, we often don’t use these features, which is why we considered cell based values first.

Let’s create the parameters we used in our example.

In Power Query, click Home > Manage Parameters to open the Manage Parameters dialog box.

Home Manage Parameters

Click New to create a new parameter, then enter the following details:

  • Name: SoldBy
  • Type: Text
  • Current Value: Sally

Click OK to close the dialog box.

Manage Parameters Dialog Box

The details for the Date query are:

  • Name: Date
  • Type: Data
  • Current Value: 31/03/2019 (this is a UK date format, use your local date format if you’re following along)

This method creates parameters we can change through Power Query’s user interface.

Queries view for Manage Parameters

While these parameters have special purposes, they are used in the SalesData query in precisely the same way.

Static values

There is another way to create an “unofficial” parameter inside Power Query.

In the primary example above, we turned a regular query into an unofficial parameter by drilling down into a single value. We can use this approach with any data source. However, we can also use any query containing a single value.

Below is an example of a Blank Query where the value is entered directly into the formula bar.

Single value query

To use these simple parameters, we open Power Query and change the values as required.

Even though these are single values queries, they can easily be converted to official parameters by right-clicking on the query and selecting Convert to Parameter from the menu.

Right click on a value convert to official parameter

Notes

Here are a few things to help you along the journey of implementing Power Query parameters

Formula Firewall & Privacy

If you start using Parameters to pass data between data different sources, you will likely encounter the Formula Firewall error and Privacy issues. For more information on this and how to resolve the problem, look at this post: https://www.thepoweruser.com/2019/03/12/data-privacy-and-the-formula-firewall/

How to select all

This method can work with multiple values, but I decided to keep things simple and not demonstrate it in this post.

If you want a switch to revert back to show all items, follow this post: Filter All in Power Query

Wrap-up

This post shows us how to create Power Query parameters in various forms. I believe the cell-linked parameters are the most valuable and flexible for Excel users. However, if we need more advanced functionality, we have also seen how to use Power Query’s built-in parameters functionality.

Power Query Series Contents

  1. Introduction
  2. Import Data
  3. Data Refresh
  4. Edit Queries
  5. Close & Load Options
  6. Using Parameters
  7. Basic Transformations
  8. Combine / Append Queries
  9. Import All Files in a Folder
  10. List All the Files in a Folder and File Attributes
  11. Import Data from the Current Workbook
  12. Import Data from the Web
  13. Unpivot Data
  14. Unstacking Data in a Column
  15. Lookup Values using Merge
  16. Change the Source Data Location
  17. Formulas
  18. If Statements for Conditional Logic
  19. Grouping and Summarizing Data
  20. Custom Functions
  21. Common Errors & How to Fix Them
  22. Tips and Tricks


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:


2 thoughts on “Power Query Parameters: 3 methods + 1 simple example

  1. Pablo Baez says:

    Hi Mark,
    Awesome content, thanks for sharing.
    I have used the technique “right-click on the value and select Drill Down” and it sometimes gives me the Firewall error when sharing with others.
    How do I fix that annoying error?
    Thanks,
    Pablo

    • Excel Off The Grid says:

      Hi Pablo – I’m assuming your parameter is used in the source file path, or a web URL?

      In those scenarios, you need to
      (a) Create the parameter inside the same query (see examples in this post:https://exceloffthegrid.com/power-query-source-cell-value/)
      (b) Ensure the privacy settings match, you do this inside the Query Source settings

      When the file is used by multiple users the privacy settings are reset each time the file is opened. This is to ensure data isn’t leaked outside of the organisation. You can use a Macro button with Query’s FastCombine parameter set to true. This will ignore privacy settings.

      I’m planning on doing a blog post about these settings soon.

Leave a Reply

Your email address will not be published.