My first article on ArcGIS Maps for Power BI was written in June 2017. It’s time for a review since the technology and my proficiency have both improved. The examples use data on mobile money agents in Kenya and help us to assess how Power BI can be used for data-driven storytelling.
ArcGIS Maps for Power BI is available with the free version of Power BI and can be accessed from Power BI Service and Desktop. For this review, we will use Power BI Desktop since it allows us to clean, transform, and model our data prior to visualization.
Note that Power BI has other map visualizations such as Map which creates a bubble map and Filled Map which creates a filled map also known choropleth. These visualizations work very well but they have fewer options for visualization and analysis.
For the demonstration, we will use spreadsheet data in Excel on Mobile Money Agents that was sourced from FSD Kenya. The original data was restructured and reduced to make it easier to understand and more suitable for analysis. The following capabilities of Power BI will be demonstrated:
- Data preparation using the Query Editor
- Basic map visualization with ArcGIS Maps
- Advanced map styling with Edit.
- Performing analysis with multiple visuals
- Filtering and highlighting
After creating a new Power BI report, we need to import the modified Mobile Money Agents data. To do this we’ll click Get Data under the File menu or on the ribbon to open the following dialog:
We select Excel as the data type and browse to our data before we hit the Load button. Here’s how the first rows and columns of our spreadsheet look like:
Be aware that Power BI doesn’t create a copy of the data but a query that imports the data from the source. This query can be edited when first loading the data or at a later stage by clicking the Edit Queries button on the ribbon. Query editing enables us to edit the data, remove columns we don’t need and create new columns from existing data.
The example below illustrates how a new year column was created from the start date field. Other data transformations were conducted in a similar fashion.
Power BI keeps track of the transformations applied to the source data as shown below. This allows us to modify or remove any of the transformations at a later stage.
To add a map visualization to your Power BI report, follow these steps:
- Ensure that the Report view is active
- Click the ArcGIS Maps for Power BI icon
- Resize and position the template
With the template selected and the Fields pane visible, you can begin to drag and drop fields into the field wells that are dependent on the template. The following field wells are available:
- Location – address or administrative unit/boundary (e.g. country) data field
- Latitude – latitude (y) value of an explicit coordinate value pair
- Longitude – longitude (x) value of an explicit coordinate value pair
- Size – numeric data field used to quantify data features by size
- Color – data field used to categorize data features by color
- Time – date field used to animate the map with time
- Find Similar – numeric data fields used to find features that are similar
The map template will populate when you drag and drop fields into the field wells. In our example we’ll do the following:
- The Latitude and Longitude field wells are filed with GPS Latitude and GPS Longitude. As soon as this is done, locations of mobile money agents are being drawn in the template.
- The Trading Hours field which indicates the number of daily operating hours is dropped into the Size field well
- The Color field well is filled with the Stand Alone field which has Branch, Conduct Other Business, Head Office and Stand alone as unique values.
- The Start Date field which represents the date when operations started is dropped in the Time field well. Filling this field well adds a time slider to the template.
- The Tooltips field well is populated with the Deposits, Withdrawals, Out of Cash, and Out of E-Float These fields capture critical business details of the mobile money agents.
The resulting map shows the distribution of mobile money agents and playing the time animation shows how they spread and increase with time. However, the display is cluttered so we can’t easily tell how many mobile money agents there are within a location.
To further style the map visualization we click on More … to access the Edit menu.
This opens a new menu bar with the following choices:
- Basemap – this menu provides access to additional basemaps (e.g. OpenStreetMap). You have more choices (e.g. Imagery) when signed into a Plus Subscription or an ArcGIS Online account. To change our basemap let’s select Dark Gray Canvas from the dropdown.
- Map theme – this menu allows us to symbolize the map features in different ways. For large collections of point features as in our example, Heat Map and Clustering are suitable options. Here’s our map after selecting Clustering as the map theme.
- Symbol style – through this menu we can style the symbols used on our map. We’ll stick with the defaults for our Clustering.
- Analytics – this drop-down menu has Pins, Drive Time, Reference layer, and Infographics as its choices. The example below shows how to add a layer from an ArcGIS Online account as a reference to show the location of mobile money agents in relation to population.
To access ArcGIS Online content, we should have signed into to our ArcGIS account. This is how it is done from the ArcGIS Maps template.
Once we are done editing our map we can click on Back to report.
Now let’s get some insights from our data by performing analysis. A simple way to do this is by creating and comparing multiple visuals.
As a first example let’s perform heat mapping to compare the number of transactions for mobile money deposits and withdrawals:
This comparison shows that in towns like Nakuru, Kisii, Kericho, and Eldoret mobile money deposit transactions exceed withdrawals. This could suggest that mobile money is used as a bank account and that mobile payments are substituting cash payments.
Let’s now look at changes over time using the time slider. The example below uses heat mapping to compare entrant mobile money agents in 2006 and 2015. It appears that new agents are primarily appearing in low-income neighborhoods (e.g. Kawangware) and fast-growing urban centers (e.g. Ongata Rongai).
Power BI models the interaction between different visuals. If data gets selected in one visual, it filters or highlights the data in the other visuals. This behavior is illustrated in the example below.
It can be observed that Nairobi County has 14,44K mobile money agents with over 10,000 of them around the CBD. We can also tell how many started operations each year from 2006 to 2015.
Filtering and Highlighting
Having seen the power of filtering and highlighting in the last example let’s dive a bit deeper. Be reminded that filtering selects the data that you want to focus on and removes all other data. Highlighting in contrast highlights a subset of the data, but the other data remains visible.
In Power BI filters are applied in the filter pane at the following levels:
- Report level filters – these filters are applied to all the pages within your report. In our report, we use a report level filter to focus on mobile money agents that started operations from the year 2006 until 2015.
- Page level filter – these filters are applied to all visuals within the page of a report. See the example below where a page level filter is used to select agents with a high number of deposits and withdrawals.
- Visual level filter – these filters are only visible when you select a visual on the canvas. They don’t impact any other visuals.
Data in the visuals can also be filtered or highlighted through the interaction with other visuals. Filtering is the default behavior for data on a map and Highlighting is the default behavior for charts. One can select a visual and click on Edit Interactions in the Format menu to change the behavior.
The ArcGIS Maps visual has a collection of tools for selecting features as shown below. Some of the tools could be greyed out since they depend on other configurations or additional content.
The default tool Select individual locations on the outer left allows us to select a single feature from the map layer. Be aware that this tool and others don’t work when you have selected Heat Map or Clustering as the Map Theme.
The next tool Select multiple locations enable you to select multiple features drawing a rectangle. The results of using this tool are illustrated in the example below where a rectangle was drawn to select mobile money agents around Kisumu.
The next tool Select locations using a reference layer gets activated when a reference layer is added. It enables us to select point features contained within a polygon. For illustration, we have selected high transaction volume mobile money agents in Makueni County.
Compared to the earlier article this has been a thorough review of the ArcGIS Map visualization for Microsoft Power BI. Amongst others we discussed data preparation, basic and advanced map visualization, and how to perform analysis with multiple visuals, filtering and highlighting.
The ArcGIS Map visualization gives access to basemaps, map themes (e.g. Heat Map), spatial analytics and ArcGIS Online content. It interacts with other visuals like charts and cards and complements these visuals for deeper insights into the data.
ArcGIS Maps is included in the free version of Power BI, but you might want to connect to an ArcGIS Online or add a Plus Subscription for advanced functionality. With Power BI you can connect to many data sources and create compelling visualizations. This makes it an excellent tool for data-driven storytelling.