My wife has always challenged me on my ability to use the "map" capability for Excel and PowerBI. As much as I would like to use the software tool to give her what she wants, I always run into issues trying to do the simplest task. This time she merely asked me to map out the location of cities with data label showing where they are located, and if they are within certain pre-defined location. Not a hard ask, you would think, except I didn't find map capability implemented in Excel, and limited capability with the Bing add-in. PowerBI wasn't much help either, unless of course I look into the ArcGIS maps, which has it's own subscription fees. I didn't get a chance to experiment with Tableau with the sample data, which I will add as a to-do for a possible future project.
The sample data file has 4 columns and 14 rows. The columns represents the location, number of stores at the location, name of the sales person and route.
Excel map results and notes -
- Bing Maps data format must follow it's required format and maximum of two numeric columns. In the example below, I used only city, state and number of stores column to produce the map.
- Excel Maps require the geographical data to separate out city and state. I had to convert the city, state column to "geography" data type under Data menu for Excel to identify the location, and apparently some data is still missing.
- Unfortunately neither is the look we are looking for. There must be a way to add a block of area and identify as target, in addition to plotting the requested data.
- With some data transformation, PowerBI generated a similar map as Bing map and I'm not sure if I can add a block of area and identify as target.
- In case you didn't know, you can markup your own map on Google map and share with people to view. Instead of the normal google.com/maps address, use mymaps.google.com web address.
- To add the city information, I added a layer and uploaded the CSV file. Not surprisingly, all the location showed up on the map. I created another layer and manually drew some blocks of area for testing purpose. All the labeling / color grouping were manually done. For the purpose of seeing the cities and target location, this map is it.
- The overall process is manual other than the data upload and identification, which I found amusing. I always thought Google would automate / simplify a process whenever they can. I used Google maps API and calculate distances between two points within Google Sheets before, so maybe there is a way to automate certain process. I will need to look into this further.
- There is a lot more to learn about the available mapping software.
- Sometimes a simple request can lead to hours of research and testing. Using the right tool for the right job will save you a lot of time.
- Mymap has a hard limit of 10K (10,000) points and 20 groups. Source: https://support.google.com/maps/thread/11556127/i-want-to-map-20k-pin-codes-but-when-i-upload-the-file-the-final-output-maps-only-2000-pin-codes?hl=en
- Additional resource / guide from google - https://www.google.com/earth/outreach/learn/visualize-your-data-on-a-custom-map-using-google-my-maps/
- 6/13/23 - you can download US county boundary in KML format from FCC website. You can import the boundary KML file and add it to your map. Below example added LA county boundary.
 |
| LA County boundary in black |
- Investigate ArcGIS capability
- Investigate Tableau mapping capability
- Investigate what portion of Google My Maps can be automated
Tools I used -
Sample Data creation - Microsoft Excel
(You can also use Google Sheet which should make it easier to create a map from Google Sheet)
Visualization - Office365 Excel with Bing Maps add-in, PowerBI, Google My Maps - https://www.google.com/maps/d/u/0/
