Power Query – Using Parameters

Posted on June 23, 2019 by 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 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

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: