Create a parameter query (Power Query)

You can use a parameter to automatically change a value in a query and avoid editing the query each time to change the value. You just change the parameter value. Once you create a parameter, it is saved in a special parameter query which you can conveniently change directly from Excel.

1. Select Data > Get Data > Other Sources > Launch Power Query Editor.

2. In the Power Query Editor, select Home > Manage Parameters > New Parameters.

3. In the Manage Parameter dialog box, select New.

4. Set the following as needed:

   Name	This should reflect the parameter's function, but keep it as short as possible.
   Description	This can contain any details that will help people correctly use the parameter.
   Required	Do one of the following: Any Value   You can enter any value of any data type in the parameter query. List of Values    You can limit the values to a specific list by entering them in the small grid. You must also select a Default Value and a Current Value below. Query   Select a list query, which resembles a List structured column separated by commas and enclosed in braces. For example, an Issues status field could have three values: {"New", "Ongoing", "Closed"}. You must create the list query beforehand by opening the Advanced Editor (select Home > Advanced Editor), removing the code template, entering the list of values in the query list format, and then selecting Done. Once you finish creating the parameter, the list query is displayed in your parameter values.
   Type	This specifies the data type of the parameter.
   Suggested Values	If desired, add a list of values or specify a query to provide suggestions for input.
   Default Value	This only appears if Suggested Values is set to List of values, and specifies which list item is the default. In this case, you must choose a default.
   Current Value	Depending on where you use the parameter, if this is blank the query might return no results. If Required is selected, Current Value cannot be empty.

5. To create the parameter, select OK.

Here's a way to manage changes to data source locations and help prevent refresh errors. For example, assuming a similar schema and data source, create a parameter to easily change a data source and help prevent data refresh errors. Sometimes the server, database, folder, file name, or location changes. Perhaps a database manager occasionally swaps out a server, a monthly drop of CSV files goes into a different folder, or you need to easily switch between a development/test/production environment.

Step 1: Create a parameter query

In the following example, you have several CSV files that you import using the import folder operation (Select Data > Get Data > From Files > From Folder) from folder C:\DataFilesCSV1. But sometimes a different folder is occasionally used as a location to drop the files, C:\DataFilesCSV2. You can use a parameter in a query as a substitute value for the different folder.

1. Select Home > Manage Parameters > New Parameter.

2. Enter the following information in the Manage Parameter dialog box:

   Name	CSVFileDrop
   Description	Alternate file drop location
   Required	Yes
   Type	Text
   Suggested Values	Any value
   Current Value	C:\DataFilesCSV1

3. Select OK.

Step 2: Add the parameter to the data query

1. To set the folder name as a parameter, in Query Settings, under Query Steps, select Source, and then select Edit Settings.

2. Make sure the File path option is set to Parameter, and then select the parameter you just created from the drop-down list.

3. Select OK.

Step 3: Update the parameter value

The folder location just changed, so now you can simply update the parameter query.

1. Select Data > Connections & Queries > Queries tab, right click the parameter query, and then select Edit.

2. Enter the new location in the Current Value box, such as C:\DataFilesCSV2.

3. Select Home > Close & Load.

4. To confirm your results, add new data to the data source, and then refresh the data query with the updated parameter (Select Data > Refresh All).

Sometimes you want an easy way to change the filter of a query to obtain different results without either editing the query or making slightly different copies of the same query. In this example, we change a date to conveniently change a data filter.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select the filter arrow in any column header to filter your data, and then select a filter command, such as Date/Time Filters > After. The Filter Rows dialog box appears.
   
   

3. Select the button to the left of the Value box, and then do one of the following:

   • To use an existing parameter, select Parameter, and then select the parameter you want from the list that appears on the right.

   • To use a new parameter, select New Parameter, and then create a parameter.

4. Enter the new date in the Current Value box, and then select Home > Close & Load.

5. To confirm your results, add new data to the data source, and then refresh the data query with the updated parameter (Select Data > Refresh All). For example, change the filter value to a different date to see new results.

6. Enter the new date in the Current Value box.

7. Select Home > Close & Load.

8. To confirm your results, add new data to the data source, and then refresh the data query with the updated parameter (Select Data > Refresh All).

In this example, the value in the query parameter is read from a cell in your workbook. You don't have to change the parameter query, you just update the cell value. For example, you want to filter a column by the first letter, but easily change the value to any letter from A to Z.

1. On the worksheet in a workbook where the query you want to filter is loaded, create an Excel table with two cells: a header and a value.
    

   MyFilter
   G

2. Select a cell in the Excel table, then select Data > Get Data > From Table/Range. The Power Query Editor appears.

3. In the Name box of the Query Settings pane on the right, change the query name to be more meaningful, such as FilterCellValue. 

4. To pass the value in the table, and not the table itself, right-click the value in Data Preview, and then select Drill Down.

   Notice that the formula changed to = #"Changed Type"{0}[MyFilter]

   When you use the Excel Table as a filter in step 10, Power Query references the Table value as the filter condition. A direct reference to the Excel Table would cause an error.

5. Select Home > Close & Load > Close & Load To. You now have a query parameter named "FilterCellValue" that you use in step 12.

6. In the Import Data dialog box, select Only Create Connection, and then select OK.

7. Open the query you want to filter with the value in the FilterCellValue table, one previously loaded from the Power Query Editor, by selecting a cell in the data, and then selecting Query > Edit. For more information see Create, load, or edit a query in Excel.

8. Select the filter arrow in any column header to filter your data, and then select a filter command, such as Text Filters > Begins With. The Filter Rows dialog box appears. 

9. Enter any value in the Value box, such as "G" and then select OK. In this case, the value is a temporary placeholder for the value in the FilterCellValue table which you enter in the next step.

10. Select the arrow on the right side of the formula bar to display the whole formula. Here's an example of a filter condition in a formula:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

11. Select the value of the filter. In the formula, select "G".

12. Using M Intellisense, enter the first few letter of the FilterCellValue table you created, and then select it from the list that appears.

13. Select Home > Close > Close & Load.

Result

Your query now uses the value in the Excel Table that you created to filter the query results. To use a new value, edit the cell contents in the original Excel table in step 1, change "G" to "V", and then refresh the query.

You can control whether parameter queries are allowed or not allowed.

1. In the Power Query Editor, select File > Options and Settings > Query Options > Power Query Editor.

2. In the pane on the left, under GLOBAL, select Power Query Editor.

3. In the pane on the right, under Parameters, select or clear Always allow parameterization in data source and transformation dialogs.