Diving deep into data analytics with Power BI: Map Visualization In Action

In this article, we will be using a very interesting dataset about earthquakes. The dataset contains the information about magnitude 6+ earthquakes from 1900 - 2013 taken via United States Geological Survey (USGS). We downloaded this dataset from Magnitude 6+ Earthquakes : Resources | Tableau Public. The dataset description says "All recorded earthquakes with a magnitude of 6 or greater from 1900 - 2013 via USGS (United States Geological Survey)". It has following attributes/columns:

time

Date

Time

latitude

longitude

depth

mag

magType

nst

gap

dmin

rms

net

id

updated

place

type

However, we will confine our focus on selected columns related to time, space/location, and magnitude of the earth quake. For this purpose, we will only be using following columns"

time: Date and time of earth quakes

latitude: Latitude of earth quake location

longitude: Longitude of earth quake location

depth: Depth of the hypocentre of earth quake

mag: Magnitude of the earth quake

place: Place of the earth quake with reference from nearest city and country

This data will need significant pre-processing for more productive results, but lets start with a quick visualization of the data in its existing form and let see how it works and how is the visualization. Just start your Power BI Desktop. Click on the Get Data and Select Excel as shown in Figure 1. Select the MS Excel file and then select the data sheet with data as shown in Figure 2. Click on the load button and after successful loading of data, don't forget to save your file Power BI file.

Figure 1: Get Data from MS Excel

Figure 2: Selecting the Data Sheet with Data

Figure 3: Instant Map Visualization in Power BI

Please start again and repeat the same steps until what is show in Figure 2. Instead of directly loading the data, click Edit. This will open the Query Editor window for you. Remove all unnecessary columns. Apply the changes and close the Query Editor. Don't forget to save the report. Now this time, after placing latitude and longitude columns into Latitude and Longitude place holders, do also add mag column into Size place holder meaning the higher is the magnitude of the earthquake the bigger will be the circle. Few of you might argue, why haven't we done the same earlier. Yes, we could have, but the intentional purpose was to convey that never over-rely on tool capabilities. The updated Map visualization in design mode should look like as shown in Figure 4.

Figure 4: Map Visualization

Wait, just look at the updated Map and ask yourself, is it correct? This is often the case that BI developers miss out the important validation part of their visualizations just because they seem to look correct. Now what is the problem, just refer to articles Tips and Tricks for Power BI Map visualizations and HOW TO OVERCOME MAP RELATED ISSUES IN POWER BI, POWER VIEW AND POWER MAP to understand the problem and one of the possible solution. Just observe the latitude, longitude, and mag fields. They have sigma sign in front of them, which means that these fields have been aggregated. And you can observe in the article that we must add city information in addition to latitude and longitude, or otherwise our latitude and longitude data will be aggregated at the state level. Oh, yes, this is a real life of a BI developer. So what's next? Its data pre-processing. We have the information of nearest city available in place column that can be used to increase the accuracy of our map visualization.

For extracting the location information from the place column follow the following steps. Open the Query Editor for your report. Select the place column and then click on Split Column button available in Transform tab of the toolbar. The Split Column has two options, either to split by "By Delimiter" or "By Number of Characters". Select the "By Delimiter option". Please refer to Figure 5 for precise visuals.

Figure 5: Split Column Feature

Figure 6: Using Split Column Feature

Figure 7: Using Conditional Column Feature

Figure 8: Map visualization with Location, Average Latitude, and Average Longitude

We will start with Map visualization available in Power BI. For simplicity, just make use of latitude and longitude columns, placing them into Latitude and Longitude place holders for Map visualization. Please do also format the Map visualization and add appropriate title. The visualization in designer mode should look like as shown in Figure 3. Don't forget to save you report. Beware that Map visualization make use of Bing map and thus brings-in its all related benefits and issues.


Once the Map 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 Map visualization of this tutorial is available at Earthquakes Analysis. It is also embedded on this blog below for inline view.


Just look at our first map visualization and ask yourself. Is it correct? Is it possible that Earthquake never happened in Japan? Now get back to your data and check the fifth row using the default data order. It says "15km NNE of Kunisaki-shi, Japan". This is what we should be cautious with. Tools are their to support us in data analytics, but we should be well prepared to use them correctly.



Once the Map 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 second updated Map visualization of this tutorial is available at Earthquakes Analysis Updated. It is also embedded on this blog below for inline view.





Select the custom delimiter option and add " of " as the delimiter. Also select the "At the right-most delimiter option". The Split Column dialog is shown in Figure 6. Click Ok to continue. This will add a new column with title place.2.


Now you can observe that new column still has null values, as few of the place data fields have no " of " delimiter. In this scenario, one possible solution will be to make use of original data value of place field. We can get this done using Conditional Column feature. This feature can be found in Add Column toolbar. Click on the Conditional Column feature and set the rules for new column as shown in Figure 7. Click OK to continue.


Now you can observe that you have data for every record in your newly created citycountry column. Apply and close the query editor. For your report, add newly created citycountry column in Location place holder for Map visualization and in parallel change the aggregate level of Latitude and Longitude fields to average. Your updated Map visualization in designer should look like as it is shown in Figure 8.


Once the location based averaged out Map visualization is finalized, 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 third updated Map visualization of this tutorial is available at Earthquakes Analysis Location Based Average. It is also embedded on this blog below for inline view.


I still believe that this Map visualization still has lot of room for improvement, however, considering the extended length of this article, we will stop at this point. To sum-up, this tutorial introduced the reader with use of Map visualization correctly and related Power BI features of Query Editor that can be handy for solving many BI problems. The next article in this series can be found at Diving deep into data analytics with Power BI: Data Preparation with Query Editor and More Earthquake Analytics. The previous article of this series can be accessed at Diving deep into data analytics with Power BI: Aggregate based Visualizations using MS Excel data. 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.

Diving deep into data analytics with Power BI: Aggregate based Visualizations using MS Excel data

This tutorial demonstrate creation of aggregate based visualizations for MS Excel data using Power BI. What we mean from aggregate based visualization here is the visualization techniques that can be used to display characteristics of certain group using aggregate of characteristics of individuals belonging to that group. In this tutorial, we will gain insight into which Sport had the most of the top earners in 2014. This blog is in continuity of a series of blogs that started with a very introductory work using Power BI and it is available at Diving deep into data analytics with Power BI: Tabular Visualization using MS Excel data, yes the starting pint. In this blog, we will make use of Power BI features that are available online for creation of reports. Similar work can also be done using the Power BI Desktop version.

In this tutorial, we will be using "Top Athlete Salaries" data sheet from Tableau Sample Data Sets: Global Sport Finances dataset. Details about dataset can be found at Diving deep into data analytics with Power BI: Tabular Visualization using MS Excel data. Before we move forward with our tutorial, please make sure that you have signed-up for Microsoft Power BI account. For Microsoft Power BI account creation, visit Sign in | Microsoft Power BI. After login, you will land to a page similar to the one shown in Figure 1.

Figure 1: Microsoft Power BI Online Account Welcome Page

On the top right corner of the page, you will find a Create menu, you can use this menu to create Dashboard, Report, Dataset, and Streaming dataset as shown in Figure 2.

Figure 2: Create Options Power BI

We will start with creating a dataset using our MS Excel file from Tableau Sample Data Sets: Global Sport Finances dataset. Click on the Create -> Dataset menu. You will be moved to Get Data page as shown in Figure 3.

Figure 3: Get Data Page

Click on the Get link under the Import or Connect to Data -> File area. The new page will ask you for selecting the source for the dataset. Power BI given us many options covering Local File, One Drive (both business/personal), SharePoint Team Sites as well as Databases and More as shown in Figure 4. We will confine our discussion with uploading MS Excel with Local File option for creating a dataset. Click on Local File option and select the MS Excel file from local drive.

Figure 4: Dataset Sources Options

After selecting the MS Excel file, Power BI will ask for, either you want to import the Excel data in Power BI or you want to Upload the Excel file to Power BI as shown in Figure 5. To uploading the data, click on Upload. link. After successful completion of upload, the file will be available under Workbooks link as shown in Figure 6. This file can be open in Power BI environment and manipulated using Excel Online.

Figure 5: Import vs. Upload Options for Local File

Figure 6: Workbooks in Power BI

 The import option requires data in file to be in a table format. For importing data into Power BI, open the Excel file, select the data that you want to import and press Ctrl + T keys. A create table dialog will appear as shown in Figure 7. Give you table a name as shown in Figure 8. Follow the same step of creating table for all data sheets. Save the file.

Figure 7: Create Table Dialog After Selecting the Data and Pressing Ctrl+T

Figure 8: Provide Table Name

Follow the steps shown in Figures 2-5 and click on Import link. Select the MS Excel file with table. It will import the file into Power BI as a dataset. After successful import, dataset will be visible in Power BI environment as shown in Figure 9.

Figure 9: Imported Dataset

For creating our report, click on Create -> Report menu as shown in Figure 2. Select the dataset for the report from the list of available datasets as shown in Figure 10 and click create link.

Figure 10: List of Available Dataset for Report

Now coming back to the question that we want to respond. The question is "Sport-wise Top Earners in 2014". Power BI gives us many options to respond to this question. I will use few visualizations that are easy to build and comprehend, however, it is not the exhaustive list and you may find many creative people doing better job on this question using different techniques and tools. Furthermore, there may be a discussion about the suitability of used visualization for the purpose. Well, they could be or could not be, but we will not get into this discussion and will keep our focus to explore Power BI features.

I will start with Treemap. Add Treemap to your designer. Add Sport field into Group and Athlete field into values. Make sure that values has count of Athlete field. Please do also format the Treemap and add appropriate title. The visualization in designer mode should look like as shown in Figure 11. Don't forget to save you report.

Figure 11: Treemap Visualization to Identify Sports with Top Earners in 2014

Once the Treemap 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 Treemap visualization of this tutorial is available at Sport-wise Top Earners in 2014. It is also embedded on this blog below for inline view.

Second option will be Funnel visualization. Add Funnel to your designer. Add Sport field into Group and Athlete field into values and color saturation. Make sure that values and color saturation have count of Athlete field. Please do also format the Funnel to adjust font and add appropriate title. The visualization in designer mode should look like as shown in Figure 12. Don't forget to save you report.

Figure 12: Funnel Visualization to Identify Sports with Top Earners in 2014

The funnel visualization of this tutorial is available at Sport-wise Top Earners in 2014. It is also embedded on this blog below for inline view.



Third option will be Donut chart visualization. Add Donut chart to your designer. Add Sport field into Group and Athlete field into values. Make sure that values has count of Athlete field. Please do also format the Donut chart to adjust font and add appropriate title. The visualization in designer mode should look like as shown in Figure 13. Don't forget to save you report.

Figure 13: Donut Chart Visualization to Identify Sports with Top Earners in 2014

The donut chart visualization of this tutorial is available at Sport-wise Top Earners in 2014. It is also embedded on this blog below for inline view.



I hope you enjoyed this tutorial explaining to beginners, How to create aggregate based visualizations using MS Excel data on Power BI online version. The next article in this series can be found at Diving deep into data analytics with Power BI: Map Visualization In Action. The previous as well as first article of this series can be accessed at Diving deep into data analytics with Power BI: Tabular Visualization using MS Excel data.