Parameter to filter a query

Introduction to query parameters

Parameters are great helpers to obtain a full control and provide high flexibility over your queries.
They are a time-saving and error reducing solution for queries that need frequent changes.
Once created, the user updates parameter settings by changing the parameter placeholder.
This saves him/her from hardcoding or modifying the query code.

The classic use cases of parameters include:

• Filter criteria
• Connection string
• File path

In this blog, I explain how to pass a parameter to filter a query (refer to the slides below for screenshots)

1. Import a dataset and create a query:
Curious people: note we can create Parameters from blank query.

2. Configure one or several parameter(s)

3. Apply the parameter in a query:
In this example, I am using Insert Step After by right clicking last command showed in Applied Steps pane:

4. Terminate by editing the M formula bar:
This expression passes Parameter1 value to filter BrandName column:

= Table.SelectRows( Demo_Table, each [BrandName] = Parameter1)

This one filters out Parameter1 value from the BrandName column filter:

= Table.SelectRows( Demo_Table, each [BrandName] <> Parameter1)

You are free to pass Parameter1 value to any other compatible column of the dataset:

= Table.SelectRows( Demo_Table, each not Text.Contains([Manufacturer], Parameter1))

You are free to configure parameters on numerical values, dates or texts.

More Power Query transformations accept parameters as arguments.
You can for example use parameters to:

Replace value Value to find/ replace fields:

Set Value and Output on Conditional columns:

Another great advantage of query parameters is they can be called in Power BI Desktop.
Doing so implies a data refresh, so make sure it is the right moment to do it!

The slides describing the filtering technique with a parameter goes below:

I hope you enjoy the read.