Diving deep into data analytics with Power BI: Pie chart Visualization and use of Unpivot feature

In this article, we will make use of NSW Crime data.This data reports "New South Wales Crimes by offence type, month and Local Government Area". The data is available in both MS Excel as well as CSV format. For loading the data, follow the steps explained in Diving deep into data analytics with Power BI: Map Visualization In Action, however, once you reach the Navigator window/dialog as shown in Figure 2 of referenced blog, click Edit button. Before moving forward, save the source file. The CSV file has column names as first row. For this purpose, we have "Use First Row As Headers" feature available in the toolbar, otherwise, Query Editor will consider the first row as data and then will accordingly detect the datatypes. Please refer to Figure 1.

Figure 1: "Use First Row As Headers" Feature

Now if you look at the data, it is very difficult to get the total of each crime crime from January 1995 to December 2012. To get it done, we will make use of "Unpivot Columns" -> "Unpivot Other Columns" options after selecting the initial four columns of "Statistical Division or Subdivision", "LGA", "Offence category", and "Subcategory" as shown in Figure 2.

Figure 2: Using "Unpivot Other Columns" Feature

This will convert all unselected columns into two columns as shown in Figure 3. Now we can use this data to create visuals about the aggregate crime in for each crime subcategory.

Figure 3: Unpivoted Columns

Convert the type of Value column into "Whole Number". Now click on the Group By feature on top left under transform tab of toolbar. And create group by using "Statistical Division or Subdivision", "LGA", "Offence category", and "Subcategory" as shown in Figure 4. Apply changes and close Query Editor.

Figure 4: Group By Feature

We will confine our visualization reporting total number of offences from Jan 1995 to Dec 2012 for "Statistical Division or Subdivision". A good canidate for this visualization is Pie chart. The Pie chart visualization in design mode should look like as shown in Figure 5. Please do add tile and do perform formatting according to your needs.

Figure 5: Pie chart visualization in Power BI Desktop

Once the Pie chart visualization is complete, it can be published on the web. The details about how to publish the Power BI report on the web refer to Diving deep into data analytics with Power BI: Tabular Visualization using MS Excel data. The Pie chart visualization of this tutorial is available at Total Offences Reported in NSW between Jan 95 and Dec 12. It is also embedded on this blog below for inline view.



To sum-up, this article introduced importing a CSV file into Power BI, identified the use of Unpivot columns feature, and showed the use of Pie chart visualization. The previous article of this series can be accessed at Diving deep into data analytics with Power BI: Data Preparation with Query Editor and More Earthquake Analytics. Similarly, the first article of this series on Power BI analytics can be accessed at Diving deep into data analytics with Power BI: Tabular Visualization using MS Excel data.