Make Your Power Query Dynamic with Parameters

Vojtěch Šíma
Mar 23
10 min read

Updated: Apr 12

tl;dr Power Query supports parameters—simple, static queries that act like global constants. They let you easily update values across your queries. A big plus: in Power BI Service, you can change them in the semantic model settings without opening the model. This article shows practical ways to use them.

Table of contents:

Introduction to Power Query Parameters:
Examples:
Summary

What's a Power Query Parameter?

Parameters in Power Query are special query objects that store values—either a single value or a list, such as numbers, text, dates, etc. They help make queries more dynamic and maintainable by reducing the need for hardcoded values. You can use them to filter data, define file paths, or switch between environments. This feature becomes even more powerful in the Power BI Service, where you can adjust parameter values directly in the semantic model settings—no need to open the report in Power BI Desktop. It’s a simple way to manage and control your data inputs more efficiently.

How to create the Power Query Parameter?

In Power Query, you can create a parameter by going to the Home ribbon and selecting Manage Parameters → New. Alternatively, you can right-click in the Queries section and choose New Parameter...

If you fancy, you can also write it yourself through Blank Query.

add parameter — Add Parameter through icons

Then in the Manage Parameter window, after clicking New, you fill the required fields,

Name, Description (optional, but recommended), Required, Type, Suggested Values, Current Value (alternatively Default Value).

The only settings you should care about here is the Type and Suggested Values.

Type: Defines data type of accepted values

Very important: don’t leave the parameter type set to Any as this will prevent you from changing its value later in the Power BI Service. The same applies to Binary, though it’s less commonly used.

Suggested Values: Changes the way how you can select Current Value:
- Any value: Manually inserted single value
- List of values:
  - Manual insertion of a list of values is done through a window similar to the Enter Data option.
  - The user can write the value manually even outside the list
  - Spawns Default Value, which is the value shown as a tooltip/description on hover.
  - The user can now select a value from a dropdown menu in Power Query and in the Report view of Power BI Desktop via Edit Parameters.
- Query:
  - Allows you to choose existing List Query as a source of values
  - Current Value is still manual
  - The user can now select a value from a dropdown menu in the Report view of Power BI Desktop via Edit Parameters.
  - This option won't offer a dropdown menu in Power Query

Note: Power BI Service does not offer a dropdown menu for parameter selection.

Edit parameters in Power BI Service

Once you have it set up and you publish your semantic model to any workspace, you can change the parameters in the Semantic model's settings in Parameters section:

Note: Power BI Service does not offer a dropdown menu for parameter selection. If the value cannot be changed, a.k.a. it's greyed out, change its type from Any or Binary to something else (back in Power Query).

That wraps up the introduction—now let’s do some cool stuff.

Synced date filter on queries through parameter

Let’s look at some real-life examples. We can use the years_to_load parameter to set up a parameterized date range that applies to every query—easy to modify and fully centralized.

We want to have a date range, right? Let’s build components as simple queries using the parameter.

startDate

This query will define the start of our range. Create a new blank query and insert this one-liner:

= Date.From( Date.StartOfYear( Date.AddYears( DateTime.LocalNow(), -years_to_load ) ) )

This query executes step by step as follows:

Get the current datetime (e.g., 2025-03-22 + time).
Subtract the number of years defined in the parameter (e.g., 3), resulting in 2022-03-22 + time.
Adjust to the start of that year, giving 2022-01-01 + time.
Convert the datetime to a date type, removing the time component.
Final result: #date(2022, 1, 1) — the clean date value.

endDate

This query will define the end of our range. Create a new blank query and insert this one-liner:

= Date.From( Date.EndOfYear( DateTime.LocalNow() ) )

This query executes step by step as follows:

Get the current datetime (e.g., 2025-03-22 + time).
Adjust to the end of that year, giving 2025-12-31 + time.
Convert the datetime to a date type, removing the time component.
Final result: #date(2025, 12, 31) — the clean date value.

Implementation to query

Now we have all we need, let's implement this to our queries. If you have a query where you would use the "Filtered rows" step to define your date range, do the following:

= Table.SelectRows(previousStep, each [date]>= startDate and [date]<=endDate )

If you define your filter in SQL for example, do the following:

"select
column1,
column2
from tbl
where date_column between '" & Date.ToText(startDate, "yyyy-MM-dd") & "' and '" & Date.ToText(endDate, "yyyy-MM-dd") & "'"

I'm converting the date to text using a fixed format that's widely accepted by most SQL engines. Alternatively, you can adjust the second parameter in the Date.ToText function to fit your needs.

That’s it! If you want to take it a step further, you can turn it into a function—but this setup is all you really need. Now, when a user wants to see more or fewer years, you can simply adjust the parameter in the Power BI Service without opening the report. On the next refresh, the updated date range will be applied.

Filter the same list of values across your queries

Another great example of how centralizing filters through parameters can be useful is when your entire report—and all its subqueries—needs to apply the same filter, such as on products. For instance, you may want to include only Products A, B, and C across all product-related queries. Let’s see how you can implement this.

Create parameter

Let’s start by creating a parameter to store product information and control all related queries. You might think it makes sense to create the parameter as a list of values—however, parameters in Power BI can only hold a single Current Value, so that approach won’t work. Instead, we can build our list from a string.

Let's initiate the parameter as Text, with the option Any value like this:

Transform parameter

Now that we have the parameter, we need to make a few adjustments to use it as a filter. I’ll show you two approaches: one for preparing it to work with a standard List.Contains filter, and another for using it in a SQL IN clause. This is a universal method—you can store it as a function and reuse it across all your filter parameters.

List.Contains option

Open a new query by selecting Blank Query and enter the following:

= List.Transform( Text.Split(products, ","), each Text.Trim(_) )

This query executes step by step as follows:

Split the product parameter by a comma.
1. This split will create a list where each item contains the content between the delimiters.
Transform the new list and trim leading and trailing spaces.
1. This step is optional, but it ensures there are no extra spaces, whether values are entered with or without spaces.
2. Based on your preferences, you can apply additional cleaning steps like Text.Clean or Text.Upper.

SQL IN clause option

Open a new query by selecting Blank Query and enter the following:

= Text.Combine( List.Transform(products_filter, each "'" & _ & "'"), ",")

This query executes step by step as follows:

Reuse the already established clean list (or build it from the parameter).
1. Add single quotes around each item using a transformation step.
Combine the quoted list into a single comma-separated string.
1. Each item is expanded and separated by a comma, the last item won't have the comma.
2. This creates a clean text string ideal for use in a SQL IN statement.

The same in pictures:

Implementation to query

Now we have all we need, let's implement this to our queries. If you have a query where you would use the "Filtered rows" step to define products to filter, do the following:

= Table.SelectRows(previousStep, each List.Contains( products_filter, [product] ) )

If you define your filter in SQL for example, do the following:

"select
column1,
column2
from tbl
where product_column in ("& products_filter_sql &")

Manage connection strings with parameters

Another great example is using parameters to define the connection string for your entire dataset. When you're developing reports across both development and production environments, manually switching each query can be a hassle. Instead, you can use a List of Values with predefined connection strings to easily manage and switch between environments.

Create parameter

Initiate Text value as List of Values like this:

Switcher parameter for SQL server environments

Implementation to query

The implementation is quite straightforward. You can use any function that allows you to define a source, as long as the source value is valid. In our case, we’re connecting to a SQL Server database, with the host defined by a parameter. We also want to execute a previously built SQL query—you can do that like this:

sqlQuery = Value.NativeQuery(
        Sql.Database(sql_server_environments, "database"), filterProductSQL, [
            EnableFolding = true
        ]
    )

In this snippet, sql_server_environments holds the host information and comes from a parameter. The filterProductSQL variable, defined earlier, includes another parameter to help refine the data filtering.

Looking at the code, we can take it a step further. Right now, the database name "database" is hardcoded—but we can pair each environment with its corresponding database to ensure everything is controlled from a single place.

Let’s create a new blank query that defines which environment maps to which database. You can use Enter Data for this, but I’ll write it manually in M code so you can see how to do it yourself.

Here are two methods, based on your preferences, pick one:

let
    // option with list of records, you then filter required env and extract first item's field db
    definition =
    {
        [env = "prod-sqlserver.com", db = "prod_db"],
        [env= "dev-sqlserver.com", db = "dev_db"]
    },
    returnCurrentDatabase = List.Select(definition, each _[env]=sql_server_environments){0}[db]
in
	returnCurrentDatabase

let
    // option with single record, where each field is name of environment and value is database, you then pick the field
    envMapping = [
        #"prod-sqlserver.com" = "prod_db",
        #"dev-sqlserver.com" = "dev_db"
    ],
    currentDatabase = Record.Field(envMapping, sql_server_environments)
in
    currentDatabase

If you used the Enter Data option, don’t forget to apply Table.SelectRows to filter the correct environment, and then convert the result to a single value—just like in the first approach.

Now, let’s update our original Sql.Database function to use the new, automatic, and centralized way of discovering the correct database for each environment.

sqlQuery = Value.NativeQuery(
        Sql.Database(sql_server_environments, sql_server_database), filterProductSQL, [
            EnableFolding = true
        ]
    )

SQL Server database function with all parameters

Considerations and alternatives

This solution has an obvious flaw—the database names are defined inside the report, not through parameters. But just like we did with the "product" setup, you can absolutely rewrite this using a List of Values, where each item is a comma-separated text string like: prod-sqlserver.com, prod_db, etc. Then you’d use the same splitting and transformation techniques to extract the values. As always, there’s no one right way—just go with whatever works best for your setup.

Parameters in Power BI Deployment Pipeline Rules

This part of the article isn’t about Power BI Deployment Pipelines as a whole; rather, it focuses on how to take advantage of parameters within the so-called Deployment Pipeline Rules.

With a Fabric subscription, you now have the option to use Deployment Pipelines to streamline the process of deploying your reports across different environments. One key benefit of using Deployment Pipelines alongside existing parameters in your semantic model is that Deployment Pipeline Rules allow you to modify the values of those parameters when promoting content to the next stage—Test or Production.

How to set up the rules

Once you’ve established three workspaces as part of your deployment pipeline, navigate to one of the environment workspaces and click View Deployment Pipeline.

Here, navigate to the environment where you want to set up the rule, and click the lightning icon in the bottom section.

Here, you’ll find all eligible Fabric items to which you can apply rules. Based on my experience, you can't configure rules for newly added items right away—you need to deploy them at least once before they become available for rule setup.

Once you click the item you want to update, you’ll have the option to choose between Data source rules and Parameter rules. In general, a Data source rule allows you to change the data source—if the source supports it. Parameter rules can achieve similar outcomes and offer greater flexibility, allowing you to adjust not just the data source, but also other elements such as filters.

We want to modify the Parameter rules, so go ahead and click on it. Once you do, you'll see the available parameters along with their current values. If you click Other, you'll be able to enter a new static value. Simply type in your desired value, hit Save, and that's it.

Just like with semantic model parameters in the Power BI Service, there's no real dropdown menu here—even if your parameter is defined as a list of values. The only dropdown you see is more of a placeholder: it shows the originally selected Current Value from when the semantic model was published, an Other option, and your most recent value (if it differs from the original).

And that’s pretty much it. Now, every time you deploy the semantic model to this environment, the parameter value will be automatically updated. You can always modify or remove the rule later if needed.

To test the rule, go to the destination workspace and refresh the semantic model manually. Keep in mind that deployment alone doesn’t refresh the data—so don’t forget that step.

Summary

Power Query parameters are a super handy way to make your reports more flexible and way easier to manage. Whether you're syncing date filters, reusing product filters across multiple queries, or switching between dev and prod environments without the headache—parameters have your back. Plus, the fact that you can update them directly in the Power BI Service makes everything smoother. This walkthrough showed you how to actually use them in real-life scenarios, not just theory, so you can keep your setup clean, centralized, and way less manual.