Power BI Desktop June 2016 Update – Part 2 – Parameters

So in my previous post, I explained how functions have changed and how you can convert existing queries to a function. In this post I’ll continue with my original issue of using multiple queries but using parameters to connect to one SQL server/db.

With the June Power BI desktop update, we can now specify parameters to connect to data sources and there are a couple of ways we can do this…

To use parameters on creation of a data source, you need to have already created a parameter prior to selecting one.

1. Load the query editor.

2. Click Manage Parameters.

3. Create two new parameters for the SQL Server and Database and add the default values in and click OK.

4. Verify you can see the parameters in the left hand queries pane.

Creating a Parameter – On Getting Data

If your query already contains parameters, you can select or create additional parameters through the below:

1. Get data. 

2. Select your data source (I used SQL Server for this example). You’ll notice that there is a couple of new drop down boxes…

3. You can select the “New Parameter” option to create (step 3 from previous section) or select “Parameter” and specify an existing one.

When done, you can follow the rest of the wizard and connect to your data source. So what about updating existing queries…?

Updating Existing Queries

In my Part 1 I talked about using a dynamic XML table as a source for my parameters. To update an existing query to use the new parameter method, we need to edit the existing reference variable below.

In the above, you can see we have our variables which currently point to my XML table. To change this to the new parameters all we need to do is the below:

This tells the query that the SCCMServer and SCCMDatabase variables are the relevant parameters and the source then uses these to connect.

If you don’t have a preset variable specified like mine, to amend an existing query, all you need to do it add variables first

Let
<VariableName0> = #”<Parameter Name0>”,
<VariableName1> = #”<Parameter Name1>”,
Source = sql.database(VariableName0, VariableName1, [Query=”

With the SQL parameters now set and tested, all I needed to do was then go through the rest of the queries and do the above then all good again!