(scottish) sql bob blog

rss

Some thoughts of your typical data shepard / data groomer / data dance teacher sort of person.


M (formally know as Power Query Formula Language), PowerBI and parameterising data sources - part one

One of the attractions of being a programmer was that l could find ways to make routine tasks easier and quicker.  As our company and clients have discovered the joy of Power BI also the reports l have created.  There are more clients are asking for reports.  Some of these are the same reports to ensure everyone has the same view of their data.

Scenario
There are two databases l populate with data, one of these is on my machine, the other is an Azure database.  As you would expect l use the local copy to test out various changes to the database.  Then using the Redgate tool SQL Compare l can script any changes and apply them to the Azure database.  It is possible to change the data source for each dataset easily using the following method.

From the main window, on the 'Home' tab click once on the 'Edit Queries' button see Figure 1



Figure 1 - showing the location of the 'Edit Queries' button

This will open the 'Edit Queries' window now to see where the data source for a selected dataset.  First select a dataset in the example shown in Figure 2, it is 'DimDate'.  Next click on the 'Advanced Editor' button see figure 2.


Figure 2 - Showing location of 'Advanced Editor' button

When the Advanced Editor window opens it will look something like the one shown in Figure 3 below.



Figure 3 - Advanced Editor window, with M (Power Query Formula Language) query showing the Database Server Name and Database Name

The syntax of the statement is relatively simple.  In this case we are looking at the Sql.Database data function, the syntax is simple and looks like this -:

Source = Sql.Database( "Name of Database Server", "Name Of Database")

This is the text shown in Figure 3

let
    Source = Sql.Database("localhost", "DBdata"),
    dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data]
in
    dbo_DimDate

To change the data source to point at the Azure database the only changes that have to made are as follows

let
    Source = Sql.Database("AzureDatabase""DBdata"),
    dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data]
in
    dbo_DimDate


Job done.  This was nice until you have to do it several times, and personally I got bored then wondered if there was a better way?  I am glad you asked if there is a better way let me show you.

Having seen some examples where the data source was an excel spreadsheet l was aware that it was possible to supply parameters to change the data source.  From one spreadsheet to another one.  
The first step is to set up the parameters.  Open the Query Editor window, click on the 'New Sources' button from the list click on 'Blank Query' (see Figure 4)


Figure 4 - Creating a new blank query

Figure 5 shows what the Query window will look like when it first opens.  Change the name of the query to 'DatabaseServer' then enter the name of the database serving into the text box.  Once you have entered the database server name, click once on the tick to save your changes.


Figure 5 - Show where to make changes to the new query that will store the connect parameter.

Having set up the DatabaseServer dataset (parameter value), repeat the process and create a DatabaseName dataset (parameter value).  Figure 6 shows what you query editor window would look like if you entered the same details shown in this example.


Figure 6 - Showing the two datasets in place containing the parameters, showing the completed 'DatabaseServer' dataset.

So you should now have two additional datasets -:

  * DatabaseServer - containing the value 'AzureDatabase' or the name of the database server you are going to connect to.
  * DatabaseName - containing the value 'DBData' or the name of the database you are going to connect to.

If we look at the advanced properties of the dataset 'DimDate' the data source would look like the one shown below.

let
    Source = Sql.Database("AzureDatabase""DBdata"),
    dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data]
in
    dbo_DimDate


Next step is to replace the database server name and database name with the names of the Datasets that have just been set up.  So the data source will look like the one below.

 let
    Source = Sql.Database(DatabaseServer, DatabaseName),
    dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data]
in
    dbo_DimDate

The advanced query window should look like the one show in figure 7 below.  When the data source is queried the values of 'DatabaseServer' and 'DatabaseName' are replaced by the values from the relevant dataset.  In this PowerBI desktop report l just replaced all the hard coded instances of the database server name and database name with the relevant parameter.  Which allows me to switch between two databases with the ease l wanted, only with PowerBI desktop :-(


Figure 7 - Showing the final M (Power Query Formula Language) text (data source)

If you click on this link you can download a copy of a .PBIX with the two dataset's shown above.  

There is more you can do with this you will need to wait till part two of this blog post for more details.