(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.


Power BI online user credentials and multiple accounts
Whilst "publishing" some reports for some of our customers from PowerBI desktop to PowerBI online. I made the assumption that the data credentials used in PowerBI desktop would be the same ones used in PowerBI online.

Scenario 
We wanted to share PowerBI reports with some of our customers. It is not possible currently to allow anyone from outwith our Azure AD group to securely access the PowerBI reports. So the only other solution was to get our Office 365 Admin to create an account for each customer. All the customers will connect to the same database to extract their data. On the database an account was created for each customer, so when they connected to the database the customers account could only access that customers data.

First step - Find credentials in PowerBI Desktop 

Open PowerBI desktop at the top left hand side click once with left hand mouse button the menu icon .  The from the menu that appears, go to 'Options and settings' menu item and click once with left hand mouse button (see figure 1)

 
Figure 1 - Showing the 'Options and settings' menu item. 
 
In the 'Data Source Settings' window select the data source (3), that is to say where the data is extracted from. Next press the 'Edit' button (4) this will open the window shown in Figure 2.

Figure 2 - First data source settings window.

In the second  'Data Source Settings' window click on the 'Edit' button 


Figure 3 - Second data source settings window

Now you can get the details eg Username and password used to connect to the data source are stored. If these are required. 
 
Figure 4 - Data source credentials window
 
So we now have the follow credentials
Username = [email protected] 
Password = JoeB123 
N.B. We will be using these details later. 

Tip if using multiple accounts with PowerBI online  
Next step would be to publish your reports to the PowerBI service at PowerBI.com.   If you have multiple accounts which you use to publish your reports to a PowerBI service. Then one tip is log out of the account and log back in as there is nothing in the PowerBI desktop application which indicates which account is logged in. 

Adding credentials in PowerBI Service (Aka PowerBi.com) 
One quick tip here is to open PowerBi.com using Internet Explorer. At the time of writing process shown next did not reliability render in other browsers.  Log into PowerBI.com once successfully log in, go to the top right hand side of the window. Click with left hand mouse button the settings icon, from the menu click on the 'Settings' menu item. 

 
Once the 'Settings' window opens click on the 'Datasets' tab 

 

 Once in 'Settings->Datasets' click once with left hand mouse button on the triangle to the left of 'Data source credentials' 



 Once the 'Data source credentials' have been expanded click on the 'Edit credential' link. 

 

 Once the  credentials configuration window opens the drop down box below the authentication method select the 'Basic' authentication method. 

 

 When you select the 'Basic' Authentication method, text boxes will appear at the bottom of the form. Enter the username and password, then click on the 'Sign In' button. 



 If the user name and password has been successful then you will see a message appear in the top right hand corner of the PowerBi window, indicating the data source has been successfully updated. 




Ordering month order in Power BI desktop

Whilst preparing a simple dataset to with a stacked bar chart in PowerBI l included both the month number and name.  The dataset is shown below

Month_Number Month_Name Total Type
1 January 61 Created
1 January 63 Resolution
5 May 14 Created
5 May 8 Resolution
6 June 24 Created
6 June 26 Resolution
7 July 33 Created
7 July 36 Resolution
8 August 51 Created
8 August 48 Resolution
9 September 126 Created
9 September 109 Resolution
10 October 147 Created
10 October 152 Resolution
11 November 98 Created
11 November 88 Resolution
12 December 65 Created
12 December 77 Resolution

The data set was created in PowerBI desktop, the next step was to plot the data on a clustered column chart.  The month name was the value l wanted to appear on the axis, there was a little challenge with the way data was being displayed.  The month names where being displayed in alphabetical order (see Figure 2)

Figure 2 - Power BI chart after placing data on the canvas

What l wanted was the month names to be sorted according to month numbers so l knew l had missed something.  First step was to go to the data tab to have a look at the data.  So I licked on the the dataset and selected the 'Modeling' tab (see Figure 3).


Figure 3 - Data view 'Modeling' tab selected the 'Month_Name' column selected

Everything looked the way l expected then with the 'Month_Name' column selected l clicked on the 'Sort By Column'.  This was sorted by 'Month_Name' so it would be sorted alphabetically, so l changed it to sort by the 'Month_Number' see Figure 4.

Figure 4 - The first figure shows the default setting for the 'Month_Name' column, the second figure shows the amended selection to sort the 'Month_Name'Column by the 'Month_Number' column

Then l returned to the report view to review the change see Figure 5


Figure 5 - The clustered bar chart report showing the Month Names sorted in the order of the 'Month_Number' column

Success the months where ordering as l wanted them, next I'm off to resolve my next challenge, of which there is never a shortage.