The last article in this series will focus on data visualisation options in relation to the data from the first article. Data visualisation is the representation of data in a visual way using elements such as charts, graphs, maps and others.
Microsoft Power BI is the application that is going to be used to represent the collected data in a more visual way. This application needs to be given access to the Renewable Energy Data that is available both as a spreadsheet (Microsoft Excel) and database (Microsoft SQL Server). Microsoft Power BI can obtain data from many of the most common data sources. In this article, we will be connecting the application to the local Microsoft SQL Server database instance where our data is currently stored. Another option is to connect to the Microsoft Excel file (Aggregate_Data.xlsx) directly.
Once that the data is made accessible in Microsoft Power BI, various data visualisation options can be included in the available canvas. In the below screenshot, the major sections within this application have been highlighted in different colors:
- Options ribbon (red section): used for the major options available in Power BI
- Modeling canvas (blue section): used to design the different dynamic reports using different pages
- Visualizations options (orange section): includes all the available visualisation option and their specific options
- Fields (green section): includes the tables and columns from the available data
Changing the title of the page
One of the most important things while compiling a Power BI report is to split information into different pages. This technique will make reports more readable and organised. This design option would be much better if suitable names are given to the respective pages. In the below we are renaming Page 1 to Sock of PV.
Adding a Title to the page
It is usually considered a good idea to include a title to a page so that it is more clear what the content is about. This can be done very easily using a text box. A text box has many different options available. These visualisation options are related to text size, color, shadow, and much more.
Modifying the available data
This application provides a number of different possibilities for data-related modifications. As explained before the section related to data can be found in the rightmost section named Fields. Amongst the most commonly available options, you can find the possibility of adding measures and columns. Typically the majority of the changes are performed via the Edit query option.
The Power Query Editor, includes many different options such as adding/removing data sources, refreshing data (to be up to date with source), keeping/removing columns, keeping/removing rows, splitting columns, changing data types, grouping, replacing values and many other operations.
In order to be able to create more meaningful visualisations, we will be performing unpivoting of columns so that we translate the available data into attribute-value pairs. It is important to note that the columns Southern Harbour, Northern Harbour, South Harbour, South Eastern, Western, Northern, Gozo & Comino are no longer columns but have been included in the data rows.
Creating a visualisation
Microsoft Power BI offers a vast variety of visualisation options. In this section we will demonstrate two simple visualisations which can be changed at a click of a button, by selecting alternative options from the Visualizations pane.
In the first visualisation we will include a stacked-column chart which includes the number of different PV installations for each year and region. Note that 3 columns were dragged from the Fields section to the chart properties and then a number of settings related to the visuals of the chart have been modified.
Note: The x-axis includes the Year but since its data type is currently set to decimal the values include a decimal point. You can change the data type of this column to Whole Number, by opening the Power Query Editor, selecting the Year column followed by the Transform tab and simply changing the data type so that the chart looks as follows:
The second visualisation is a Pie Chart which displays the number of PV panel installations per region (Domestic, Commercial and Public)
Dynamic filtering of Data
An important feature that is available within Microsoft Power BI is that of filters. Filters can take different formats but they are essentially used so that the user of the report can drill down into the details of the data. In this report, we will add two filters, where the user will be able to select between the different Sectors and another for the Year.
In the below clip you can see the two filters in action, where the values being displayed are changing according to the filter options that the user is selecting.
