Power BI already contains some data source connections that allow you to use parameters when you are defining the properties for the connections; examples include SQL Server, Salesforce Objects, and SharePoint. Let’s say that you want to get some data from SQL Server; you can use two parameters – one for the Server instance and one for the target database.

The parameters are not dependent on any dataset, and that means they can be created before or after you add a dataset. However, they must be defined, and their initial values must be set in Query Editor. When you create a parameter, you will see it in the Queries pane; here, you can also view the values, update, and reconfigure settings if needed.

We are going to retrieve some data from an SQL Server database, and, for that, we need a pair of connection parameters. The first one will list all the Server instances that may be a host for the source data – for our example, we only need a single instance; the rest will just demonstrate having multiple instances.

First, open Query Editor and go to the Home ribbon. Click on the down arrow beside Manage Parameters and click on New Parameter. The dialog box opens, go to the Name text box and type SlqSrvInstance. Go to the Description box and type in a description for it.

Click on Type and choose Text from the drop-down menu. Then click on Suggested Values and click on List of Values. A grid will open; this is where the individual values are types for assigning to the variable. Make sure that a minimum of one value matches the name of a real SQL Server instance. The values to type in for this example are:

.\SqlSrv17a

.\SqlSrv17b

.\SqlSrv17c

When the values have been typed in, choose a default from the drop-down menu, and then go to the Current Value menu and choose the current value for the variable. We’ll use SqlSrv17a as both values.

Close the Parameters box by clicking on OK, and the parameter will be placed int eh Queries pane; the parentheses show the current value. When you click to select a parameter, you will see the button for Manage Parameter and the Current Value menu in the primary pane. To change the current value, choose a new one from the menu or change the settings through the Manage Parameter.

Now we need a target database parameter; follow the same process, but make sure the parameter is named as Database. Make sure a minimum of one real database is shown in the list. After you click OK, the parameter is added to Queries again. For our database, we will use the values:

AdventureWorks2014

AdventureWorks2015

AdventureWorks2016

That’s it; you have created two connection parameters. Your parameters can be configured with other data types if you require them, such as dates or decimals, or different formats for the values. For example, the parameter can be changed, so it takes any value or one from a list query. List queries can be created manually with an M statement or based on a dataset.

We are going to run a T-SQL query but, first, you need to make sure that you have disabled the property for Require User Approval for New Native Database Queries. If it hasn’t been disabled when you run the query, you will get an error. To check the property, click File>Options and Settings>Options. When the dialog box opens, click on Security and, if the property checkbox is ticked, clear it.

Back to the Desktop window, click on Data view and, in the Home ribbon, click Get Data. In the dialog box, go to the Database and click on the SQL Server database.

Click on Connect, and a new dialog box appears for SQL Server Database. The top part is the Server section; go to the first option, on the left, and click it; click on Parameter. Option two changes to show a drop-down menu where you will see your newly created parameters. Choose SqlSrvInstance and then do the same for the Database section, this time choosing Database.

Click the arrow for Advanced Options and type the T-SQL statement below into the box for SQL Statement:

SELECT h.SalesPersonID AS RepID,

  CONCAT(p.LastName, ', ', p.FirstName) AS FullName,

  CAST(SUM(h.SubTotal) AS INT) AS SalesAmounts

FROM Sales.SalesOrderHeader h INNER JOIN Person.Person p

  ON h.SalesPersonID = p.BusinessEntityID

WHERE h.SalesPersonID IS NOT NULL

  AND YEAR(h.OrderDate) = 2013

GROUP BY h.SalesPersonID, p.FirstName, p.LastName

ORDER BY FullName ASC;

Click on OK, and you will see a preview window in Power BI Desktop, showing the two parameters at the top. Click on Load, and the dataset is added to the Data view. Before you go any further, give the database another name, RepSales, or something like it.

When you define the connection property parameters, their values can be changed whenever you need to, say, for example, if you needed to get data from another database or server instance. The parameters you create are not limited to single-use either; you can use them in other datasets, eliminating the need to repeat the information whenever you need a database for the same data source.

Later, we’ll look at how to work with parameters after you add them to the dataset. Now, launch Query Editor and go to Applied Steps. Click on Source and look at the M statement that goes with it. Look carefully, and you should see that the Database and SqlSrvInstance parameters are referenced by the Serve connection data. You will find them in the SQL.Database function, as the first argument. You can see the statement here:

= SQL.Database(SqlSrvInstance, Database, [Query="SELECT h.SalesPersonID AS RepID,#(lf)  CONCAT(p.LastName, ', ', p.FirstName) AS FullName, #(lf)  CAST(SUM(h.SubTotal) AS INT) AS SalesAmounts #(lf)FROM Sales.SalesOrderHeader h INNER JOIN Person.Person p#(lf)  ON h.SalesPersonID = p.BusinessEntityID#(lf)WHERE h.SalesPersonID IS NOT NULL#(lf)  AND YEAR(h.OrderDate) = 2013#(lf)GROUP BY h.SalesPersonID, p.FirstName, p.LastName#(lf)ORDER BY FullName ASC;"])

The implication here is that it is incredibly easy to reference parameters in an M statement, and this gives you a powerful, flexible way of customizing your dataset’s applied steps.

The Filtered Rows step is not always easy to update and can be inflexible, so, rather than that, you may opt to use parameters to filter datasets. An example of where you could do this would be in your T-SQL statement; in the WHERE clause in that statement, 2013 is hard-coded, and you could use a parameter in place of this:

YEAR(h.OrderDate) = 2013

Parameters can be used in place of hard-coded values, so long as the parameter has support for a range of values. To do this, you create a parameter in much the same way as we did earlier. This time though, the parameter should be called SalesYear, a list defined to contain the years 2011 to 2014 inclusive, and the current and default values set to 2011.

Once the parameter has been created, the M statement that goes with the Source step for your database should be updated to use the following code in place of the 2013 value:

" & SalesYear & "

When the code has been updated, ensure the checkmark beside the statement is ticked; this will make sure your changes are properly formatted and that the code didn’t accidentally get broken.

Next, we need to test our variable. From the Queries pane, click on SalesYear and pick a year that is different from the default of 2011. Then select the dataset (RepSales) again and check the data is updated.

Sometimes, you may want to control the logic of a query using parameters instead of filtering the data. Take the T-SQL statement from earlier, for example. The SELECT clause comes up with each reps’ total sales by using the SUM aggregate function:

CAST(SUM(h.SubTotal) AS INT) AS SalesAmounts

You could add a parameter to the M statement so that another aggregate function can be applied. First, we must create a new parameter, name it AggType and then a list must be defined with an item for each individual function:

SUM(h.SubTotal)

AVG(h.SubTotal)

MAX(h.SubTotal)

MIN(h.SubTotal)

Set the default value and the current value as the SUM function.

The reason we have the SubTotal column is partly so the logic is clear and partly to show you that the data can be summarized using other columns that your dataset supports. For example, your dataset may have a column titled DiscountAmounts that shows total sales minus discounts applied. In a case like this, the column and function can be defined by each parameter option, including values like AVG(h.SubTotal) and SUM(h.DiscountAmounts).

Once the parameter has been created, the M statement for the Source step should be updated by using the code below in place of the hardcoded fragment of SUM(h.SubTotal) – don’t forget to include the quote marks:

" & AggType & "

We can do something much the same with the ORDER BY clause used originally, and, to do this, we would need to use a variable that offers options on ordering the data. First, we need a variable called ResultsOrder. Next, for every option:

FullName ASC

FullName DESC

SalesAmount ASC

SalesAmount DESC

We need to specify the column we want the sorting based on – SalesAmounts or FullName – and we need to specify ascending or descending order. For this example, we use FullName ASC for the current value and the default.

Again, the M statement must be updated after the parameter is created, using the code below, with the quote marks, in place of the code fragment FullName ASC:

" & ResultsOrder & "

Learning how parameters are incorporated into M statements gives you many options for data manipulation and giving your datasets more flexibility without needed to add loads of steps to the query. Just make sure that every change is saved and applied, so you don’t lose it, and you know it works.  

Something else we can do with parameters is to use them to give objects dynamic names. You could, for example, rename the SalesAmount column with a name that reflects what the sales year is and the applied aggregation type. The easiest way of doing this is to add a new step to the dataset in Query Editor, call it Renamed Columns, and then update the M statement with the parameter values.

To do that, go to the SalesAmount column header and right-click it. Click on Rename and give the column a temporary name of Sales. Press the Enter key, and the step is added to Applied Steps, together with the M statement below:

= Table.RenameColumns(Source,{{"SalesAmounts", "Sales"}})

Incorporating the two parameters, AggType and SalesYear, requires that the hardcoded Sales  in the M statement be replaced with:

Sales (" & SalesYear & "-" & Text.Range(AggType, 0, 3) & ")

Note that we used the & operator. This concatenates or joins Sales with the values for the two variables, separated using a dash and contained in a set of parentheses. The function called Text. Range will only get the first three characters from the variable called AggType.

Once the statement has been updated, verify that your changes work. Note that the name of the third column in the dataset now has a set of parentheses enclosing the year and the function; this makes it far easier to see the values given to the dataset.

Earlier, we looked at changing parameter values in Query Editor. To do this, you need to go to the Queries pane and choose the parameter you want to change and then update it as needed. However, this a time-consuming process, more so when you have several parameters that you want to change, one after the other.

Thankfully, there is an easier way to do this – in Data view. To do this, go to the Home ribbon and click the arrow beside Edit Queries. Click on Edit Parameters, and a dialog box named Enter Parameters opens. Choose which values you want applied to the dataset and click on OK.

It is fair to say that you might find the dialog box name of Enter Parameters and the option Edit Parameters to be somewhat misleading,  but they do represent effective features for updating the values of your parameters and the data itself. However, you should be aware that any changes made here will be applied across any dataset that uses that parameter. If you want to use a similar parameter in several datasets, but you don’t want them all to have the same values, you will need to create dataset-specific parameters, giving them relevant names that you can easily tell apart.

When you know how these values are set, you can play about and try out different values, making sure you look at the dataset each time to see what effect the new settings create. For example, the changes we just applied to our dataset would bring up a list of three columns – RepID, FullName, and Sales (2013 – AVG)

The Sales column now has a new name, with the year and aggregation type enclosed in parentheses. Also, note that we sorted the data based on the values in the Sales column. If you are in Report view, you can change parameter values so you can immediately see those changes in the visualizations.

It is clear that these parameter capabilities boost the power and flexibility that Power BI Desktop already offers, and there are several ways to use these parameters, irrespective of the data source. Get to grips with using these parameters, and you will find them incredibly effective, and you will have m