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: Tabular Visualization using MS Excel data

This tutorial is all about making use of Power BI for data analytics. To start, again, its all about data. Interestingly I will be using sample data set that I found from Tableau online samples. Yes, I said Tableau, just because I download it from their site, but data is from ESPN. The title of dataset is "Global Sport Finances" and description says "The top paying pro sports teams and the top paid athletes via ESPN." The data set can be found at Tableau Sample Data Sets: Global Sport Finances given that you are signed-in into their public website, and yes it free. Please go ahead. Why I selected this dataset, the only reason is that I am interested in sports and this will spark my interest in getting insight from data. The data is in Microsoft Excel Format and it contains two two data sheets names "Top teams Payroll List" and "Top Athlete Salaries". "Top teams Payroll List" rank teams according to their "Average Annual pay per player". The attributes/columns/fields in this data sheet are:

Rank

Last Year's Rank

Team, League

Average Annual pay per player

% change from last year's survey

Total Payroll

Rank of Total Payroll

Average player 5 year earnings

% change over last 5 years

"Top Athlete Salaries" data sheet list the top athlete earners in 2014. For me, interestingly, the name is from Boxing. I was expecting someone from Golf, Tennis, Formula 1 or Soccer. The fields in this data sheet are:

Athlete

2014 Pay

Salary/Winnings

Endorsements

Sport

During this tutorial, I will be working with Power BI Desktop. Your startup screen should look like the one given in Figure 1.

Figure 1: Welcome Screen for Power BI Desktop

First task, again its all about data. Get data into your Power BI Desktop. Power BI given us freedom to use many (almost all) different types of data sources. Just go ahead with Microsoft Excel for this time. The dialog should look like as shown in Figure 2.

Figure 2: Get Data Dialog

Once you finish selecting the Excel file. The navigator dialog will show you all the data sheets that are available in the selected excel fine and it will also give you a preview of the data as shown in Figure 3. To load the data just click "Load".

Figure 3: Navigator with Data Preview

Once you will get finished with loading data, your Power BI Desktop windows should look like as shown in Figure 4. Please don't forget to save you work before you move forward. You will find the loaded data sheet information of the right side of the window under title of Fields.

Figure 4: Power BI Desktop Designer View

We will start with simple plain examples, and then will move forward gradually increasing the complexity of our analytics. For those obsessed with large and complex data, please keep patience. The target of this tutorial to get individuals learn from scratch. We will first start with simple tabular representation of "Top Athlete Salaries" data. We will first focus on this data sheet for exploring all important features of Power Bi Desktop and then we will move ahead with other data sheet and probably other data sets. On right hand side of window shown in Figure 4, you can observe the Visualizations pane. Before we move forward, do observe that we already have two types of filters available in Visualizations pane, i.e., Page level filters and Report level filters. Just keep simple rule in mind, irrespective of what reporting tool you use, we have to think at each level of report that what data will contribute for creation/rendering of given level. The most common levels are Report -> Page -> Rows. However, we also have other possible levels depending on type of report, such as column and group. If you want some work done or data presented at each of these level, their frequency will be defined by the level selected. For example, Report means to be executed/done when report is rendered, similarly Page means to be executed/done at each page, and similarly Row means to be executed/done for each row. Now, before you get bored with this conceptual discussion. Select the Table visualization from Visualization pane as shown in Figure 5. Add all fields by selecting it from Fields pane. You can observe that on addition of Table visualization, an additional filter of visualization level filter is added in Filters area of Visualization pane as well as Values region is added in Visualization pane where you can observe that each field that you will add to your report, it will get listed in Values region.

Figure 5: Adding Table Visualization in Power BI Desktop

After adding all fields. Resize the table in designer to a suitable width and height using the selectors around the table. Now click on any area in designer and select the format symbol in the Visualization pane. The format symbol is "Paint Roller". Just click it. Update the Page Information and Page Size values according to your design preferences. Be critical about the page size information. If you visualization is not intended for printing purpose then you can set it according to your visualization need, however, if it is to be paper printed regularly, do make sure you have selected the size the prints best according to your client/business needs.  I update the name field in Page Information and Type field to Letter in Page Size. After completing the page formatting, select the table in designer view and set the formatting for the table. I recommend to increase the size of text from 8 to 12 in General properties. You can also set the height, width, as well as x and y positions to display table. You can change table style, I prefer the Bold Header style. Grid setting enables use to set either we want to display grid or not and how it should be displayed. I prefer to display both horizontal and vertical grid for this report. You can explore other options on your own, but don't forget to enable the title for the report. Also put a suitable title. The updated view of report in the design mode is shown in Figure 6.

Figure 6: Tabular Report using Power BI

To reorder the column arrangement, one can simply drag and move columns to required position. We still lack an information in our tabular view. What is the rank? Yes, it is obvious that first row is for individual with first rank, but visualization is about conveying maximum information in first glimpse. To get this done, click on Home -> Edit Queries in the toolbar as shown in Figure 7.

Figure 7: Edit Queries Button in Home Toolbar

As you will click the Edit Queries Button, a separate Query Editor window will open. In the toolbar above, click on the Add Column tab. Click on Index Column and select "From 1" as shown in Figure 8.

Figure 8: Adding Index Column

After adding the index column, don't forget to rename it and change its data type into Whole Number. You must observe that every action that you perform in Query Editor has representative step added into Applied Steps region in Query Settings pane on the right hand side. Similarly, observe that for each action there is a representative DAX expression that can be found at top of the tabular data view underneath the toolbar. Don't forget to click on the apply button in the Home toolbar to apply the changes. Or you can simply click the save icon in the top menu bar and Power BI will confirm either you want to apply changes or revert them. After completing the apply and close operation in the Query Editor, don't forget to add the new added Rank column into your report at the correct position. Also ensure that you report is sorted on Rank column in ascending order. Now its time to publish our report online. Before you proceed, please create you profile on Power BI public, yes, its free. For Microsoft Power BI account creation, visit Sign in | Microsoft Power BI. Once you are done with you account creation click on Home -> Publish button in toolbar. It will ask you for credentials to sign-in into your Power BI public account as shown in Figure 9 and 10. Once you have provided the correct credentials, it will show you the success prompt as shown in Figure 11, if every think worked correctly for you.

Figure 9: Power BI Desktop Sign-in Prompt for Power BI Public

Figure 10: Power BI Desktop Password Prompt for Power BI Public

Figure 11: Power BI Desktop Successful Publish to Power BI Public Prompt

Good, now its time to use you published report on your online resources. Every work that we publish from Power BI Desktop is available and accessible on your online Power BI account as shown in Figure 12.

Figure 12: Power BI Public

Click on the newly published report and the report will be viewed online. Good part is that you can still edit your report online in the browser. But, beware, you changes will not automatically be transferred to your local copy. I personally prefer to always perform changes locally and uploading the latest version. But hay, wait a moment. All what we have done using Power BI Desktop can also be done online your Power BI online account. We will use the online version in our next tutorial. To use your report in other online systems, generate the embed code using the File -> Publish to Web menu as shown in Figure 13. A new dialog window will appear with option to create embed code as shown in Figure 14. Once you click on the Create embed code, a new dialog confirming that you want to publish and you have ensured following: "Do not publish confidential or proprietary information. If in doubt, check your organization's policies before publishing.". Well sounds reasonable. One should be careful about it, good part is, we used public data and properly referenced the sources above. The dialog is shown in Figure 15. Please move ahead.

Figure 13: Publish to Web Menu

Figure 14: Create embed code Dialog

Figure 15: Confirmation Dialog Before Publishing Report 

Creating the embed code will give you three information. One will be a link to share your report. Our report is available at Top Athlete Salaries 2014. Second, it will generate iframe code to embed report in online resources either its website, web-application or blog. Our report is shown embedded below:

Third information is the size of the iframe. You can adjust it using the combo box, but it can also be edited in your generate iframe code. For example, we change the width to 500 manually in generate embed code. Good, now its time to close down on this blog to avoid it from getting unreadable. Before we finish it up, this blog guides a new use to start working with Power BI using its desktop version. It only covered the Excel data and Table visualization.

I hope you enjoyed this tutorial explaining to beginners, How to start using Power BI Desktop for analytics. The next article in this series is Diving deep into data analytics with Power BI: Aggregate based Visualizations using MS Excel data.