Power Query Parameters

Why parameters make a query reusable

A Power Query parameter stores a value that a query can use. Its purpose is to separate a changeable input from the rest of the query design: the developer can replace the input without rewriting the complete query.

Imagine a sales report backed by SQL Server. The SQL query already returns sales for one salesperson, but the developer needs to produce the same view for different members of the team. A parameter named SalesPerson can supply the selected salesperson value to the native query. The report logic stays in place while the value that controls the returned data changes.

This is a developer-controlled form of a dynamic report. The selected parameter value determines which data the query returns, so one report can serve more than one purpose and reduce the need for separate copies of the report.

Keep the value, the query, and the result conceptually separate.

In this single-value pattern, the report displays data for one selected salesperson at a time. Changing the active value lets the same query return another salesperson's data; it does not redesign the query.

A parameter has no effect merely because it exists. If the source query still contains its original fixed salesperson value, changing the parameter cannot alter the result. The connection between the source expression and the parameter is therefore essential.

Connect with the initial native query

The workflow starts with a working SQL query. In Power BI Desktop, select Get data and connect to SQL Server. After entering the server details, use the SQL Server database window to submit the query:

1. Expand Advanced options.
2. Paste the query into the SQL statement box.
3. Select OK to connect.

When the connection succeeds, Power BI shows the data in a preview window. Inspect that preview to confirm that the initial query returns the expected data. Then select Edit to open the data in Power Query Editor.

At this point, the query can still contain a fixed salesperson value. That is intentional: first establish that the connection and native SQL work; then create the parameter and bind it to the query.

Each command has a distinct job in this opening sequence.

Do not confuse the SQL statement box with parameter creation. Advanced options lets you submit the native query, but it does not define SalesPerson. Likewise, selecting Edit opens the query for preparation; it does not yet replace the fixed value.

The useful mental model is connect, preview, edit. Only after the query is available in Power Query Editor do you create its named input and revise the source expression to use it.

Create the SalesPerson parameter

Once the data is open in Power Query Editor, create the value that the source query will consume. On the Home tab, select Manage parameters, then New parameter.

Use a descriptive name that communicates the parameter's purpose. For this example, replace the default name with SalesPerson. A stable, meaningful name matters because that name will be written into the source query expression. The active salesperson value can change later without renaming the parameter itself.

This command is specifically for definition. New parameter creates a named parameter; Edit parameters is used later to replace the value of a parameter that already exists. Applying or loading commands also do not create a parameter: they commit work performed elsewhere in the workflow.

Configure SalesPerson with these settings:

Select OK to confirm the definition. Power Query then displays the parameter as a new query, which you can select and inspect.

In this example, the SalesPerson parameter uses the Text data type. Type and Suggested Values are separate settings: Text is the data type, while Any value is the Suggested Values choice. You do not change one into the other before each run, and you do not need to change the parameter type when testing a different salesperson. The input changes while the definition remains stable.

Bind the parameter to the source query

Creating SalesPerson does not automatically make the source query use it. The native query must explicitly reference the parameter in place of its fixed salesperson value.

In Power Query Editor, right-click the source query—Query1 in this example—and select Advanced Editor. Find the existing fixed value in the execute statement. Replace that fixed value with an ampersand (&) followed by the parameter name, SalesPerson.

The important change is not the query's display name. Renaming Query1 to SalesPerson would leave the execute statement unbound. Removing the execute statement would remove the native query instead of making it adjustable. The required operation happens inside the source expression: the expression must incorporate the parameter's value.

Think of the edit as a before-and-after substitution:

After the replacement, check the bottom of Advanced Editor for errors and select Done. Power BI runs the query with the parameter value, even though the main screen might not show an obvious visual change at that moment.

This binding is the decisive step. Current Value can only influence the returned data after the source query references SalesPerson. If a later test still returns the original salesperson, inspect the Advanced Editor expression first: it may still contain the old fixed value.

Test the parameter in Power Query

Testing proves that the source query is bound to the parameter before you rely on it in the report. Select the parameter query and enter a different salesperson in the Current Value box.

If the binding is correct, this new value becomes the input evaluated by the native query. If the returned data does not change, do not keep renaming the parameter or changing its type. Return to the source query's Advanced Editor and verify that the fixed value was actually replaced by the SalesPerson reference.

Current Value is a field for testing or changing the parameter's active value. It is not a command that creates a parameter, and it does not insert the parameter reference into the native SQL expression. Those earlier definition and binding steps must already be complete.

Running the changed value can trigger a warning icon beside the query. The message can state that permission is required to run the native database query. When that happens, select Edit Permission and then Run. Follow this sequence:

1. Select the affected query to view the warning.
2. Select Edit Permission.
3. Select Run.

Creating another parameter, enabling load, or refreshing the preview does not grant the requested permission. The warning concerns authorization to execute the native query, so it must be handled through Edit Permission and Run.

When the query runs successfully, the parameter displays the new value. Select Close and Apply to return to the report editor with the resulting data. Close and Apply completes this editor phase; it neither creates the parameter nor binds it to the source query.

Modify and apply an existing parameter

After the parameter and its source-query reference exist, use the report editing workflow to replace the active value. Select Edit queries, then Edit parameters. In the Edit Parameters window, enter the replacement salesperson value and select OK.

After changing a parameter through Edit parameters, confirm the value, select Apply changes, and rerun the native query if required. The query then returns data for the new value passed through the parameter.

The full sequence is therefore:

Edit queries → Edit parameters → enter value → OK → Apply changes → rerun native query if required

Do not skip Apply changes: entering a value is the edit, while Apply changes commits it to the report workflow. Do not create a replacement parameter or connection; the existing parameterized query is designed to accept a new value.

Editing a parameter changes the input, not its design.

For example, if the report currently returns sales for one salesperson and the developer needs to test another, change the existing parameter value, apply the changes, and rerun the native query when required. Keeping the old Text value returns the old salesperson; replacing the SQL Server connection is unnecessary.

This reuse is the practical payoff: the same report and query design can return a different single salesperson's data simply by receiving a different active value.

Diagnose the workflow by stage

Most parameter problems become simple when you identify the stage that failed.

This map also explains the wrong fixes. Navigator is not used to turn a parameter into a model table. Apply changes does not create or bind a parameter. Credentials do not replace the SQL statement. Every command belongs to one specific stage.

A complete implementation creates a Text parameter and replaces the fixed salesperson value with its query reference. In this example, that means both of these steps:

1. Define SalesPerson as a Text parameter with Any value under Suggested Values.
2. Replace the native query's fixed salesperson value with its & SalesPerson reference.

Creating only the parameter leaves the SQL fixed. Changing Current Value without an existing, referenced parameter provides no value that the source expression can evaluate. Removing the native SQL statement also fails because it eliminates the query that should consume the parameter.

Remember the durable chain:

Connect → create → bind → test → permit → apply.

The chain preserves the roles of all three actions: New parameter defines the named, typed value; Advanced Editor binds it to the native query; Edit parameters replaces its active value later. When those roles remain distinct, one report can safely return data for different selected salespeople without rewriting its query.