There has been a lot of buzz in the BI community since yesterday, and the reason is none other than the public preview of Office 2013 (Excel 2013 in particular). There’s been a lot of articles written on the new features available and the integration of PowerPivot and Power View has really got everyone talking about. In case you still haven’t looked at the new features, here is the link for it. Meanwhile I can’t wait to blog about the spatial capabilities available in Power View, so I am directly jumping to the topic.
Power View in Excel 2013 has the ability to create maps from your data and uses Bing maps for the same. Unlike the maps in SSRS, here you do have the ability to zoom and pan as needed. Also, Bing maps automatically detects the location and hence you don’t need to provide a shapefile or even the latitude/longitude information. In this blog, I will take you through the steps to create a map report in Power View.
I) Creating a basic map report
1) Open up a new workbook Excel 2013 and then enter the following data in the cells
Country | City | Sales |
USA | Charlotte, North Carolina | 100 |
USA | Madison, Wisconsin | 50 |
USA | Jacksonville, Florida | 140 |
USA | Rochester, New York | 40 |
USA | Philadelphia, Pennsylvania | 120 |
2) Select the entire data, go to the Insert tab and click on Power View icon.
You should get a loading screen while it takes a couple of seconds to open Power View
3) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map icon as shown in the image below
You should get a warning to enable content as the data needs to be sent to Bing to get geocoded. Click on enable content to proceed. Note that you would need an internet connection for implementing this.
4) Now you can pretty much rearrange your fields by dragging them into the areas below. I have dragged Sales into the Size, City into Locations as well as Color. So I get a map report below which shows the cities as bubbles with corresponding colors and size as the amount of sales.
5) You can also play around with other properties like Title, Legend, Data Labels and Map Background. They are present when you click on the Layout tab.
It is interesting to note that the data used for creating the Power View report gets imported into PowerPivot by default. This is because Power View can only communicate through DAX currently, and hence needs a tabular model behind it. The PowerPivot model can be viewed by clicking on the PowerPivot tab and then selecting the manage tab.
Since there is no way to add additional data into this model without deleting and recreating the table, it would be a good practice to create the PowerPivot model first from linked tables, and then using the PowerPivot fields to create the Power View report. This way, we will be able to keep on adding data as long as there is a link between the table and PowerPivot. I will be showing you how to do this in the next part.
II) Creating a drill down in map report
1) Select the same set of data in excel, go to the PowerPivot tab and select the Add to Data Model icon as shown below.
Note that you can still create Power View reports directly, but we will be using this technique for the reasons mentioned above earlier. 2) Select the home tab, then click on Pivot table option and select Power View to create Power View report.
It is recommended to set the reporting properties of the Country and City field, so that Power View can recognize them as geographical entities, as shown in the image below.
3) Now you should be able to see Power View (in case you don’t, you can click on a blank cell, go to the Insert tab and click on Power View icon). Notice that the Country and City fields have a map icon
4) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map and then drag both the country and city fields within locations. You can also put the Sales measure in the Size area. Now you should have a map report which drills down from the Country level to the city.
If you double click on the blue dot in the center (which is USA), you will get the drill down report by cities. Notice that the title has automatically changed from Sales by Country to Sales by City. You can also click on the up arrow (highlighted in the image below) to return to the parent report.
You can also create hierarchies in your PowerPivot model, and that can be directly added dragged and dropped to the Locations area. This will ensure that you don’t have to drag and drop each field individually and the drill down would be present across the entire hierarchy. You can also see the benefit of adding the linked table to the PowerPivot model instead of using a range. Now if I have to add data, I can just go to the excel sheet and append the rows that I want as shown below
Now, all I need to do is to go to my Power View sheet and refresh the report. You can see that the new data is already included in the report
III) Creating Pie charts in map report
1) Use the previous report, and then add the city to the Color area. Now you can see that there is a pie chart by cities at a country level.
2) You can hover the mouse on the pie charts, and the pie chart will expand and show the tooltips as shown below
You can also use the horizontal / vertical multiples location areas to split it by the selected field as shown below
Similarly, you can use the Tile By option also (however, this feature is not available if you use the multiples option) Another best practice is to concatenate your city names with state/country info also (as I have done in all the examples) as there might be duplicate city names and doing this will help Bing in geocoding it better. You can also do this operation as a calculated column in PowerPivot, if you don’t want the city names to be displayed with their country/region info appended.
IV) Creating a map report with latitude and longitude
1) Let’s say we already have fields in the database with longitude and latitude, and we want to use them instead of Bing geocoding for us. For the purpose of this demo, let’s use the data below and paste them into excel
Country | City | latitude | longitude |
Australia | Melbourne, Australia | -37.8136 | 144.9631 |
Australia | Sydney, Australia | -33.8737 | 151.2069 |
Australia | Brisbane, Australia | -27.4709 | 153.0235 |
Australia | Perth, Australia | -31.9529 | 115.8573 |
Australia | Adelaide, Australia | -34.9287 | 138.5999 |
I used the site http://www.findlatitudeandlongitude.com/batch-geocode/ to get the latitude and longitude of the address. 2) Now select the data and use that to insert a Power View report. Click on the City field and then select the Map option. Now the latitude and longitude would be mapped to the corresponding columns if the names are the same (else you can always drag and drop them) and your map report would be ready.
Hopefully this post has given you a good start to start playing with your own map reports in Power View. There are again a lot of best practices embedded within the post and sorry for not doing a great job of consolidating them, but I am hoping that this will serve as an incentive for you to read this long post completely. And well, if you have not yet downloaded the office preview, do it now from this link.
Fantastic! The map feature is truly useful, especially the ability to use it at latitude and longitude level.
Julie
Microsoft realized Excel is the tool to do everything in when it comes to self-service BI 😉
It's going to be interesting to see how long it will take businesses to adapt to the change…and I suspect some IT people will struggle even more with the change as it is Excel :p
Great improvements!
Emil
@Julie: Yups, I am also really ecstatic at this development. However, I really wish that the POLYGON and LINES also could be made available (right now we just have points). Wishing for multiple layers might be too much at the moment, but we do have all these capabilities in SSRS, Tableau, etc
@Emil: Definitely! I love the direction which Microsoft is going currently.
Awesome, this is really nice to hear that we can create maps in Excel. I would like to tell that the map feature is newbie and might be helpful to users of it. I am really impressed from this an informative article.
Excel courses sydney
could we change the marker icon?
Not possible (atleast out of the box).
Wow what an amazing new feature
Question though, Can you drill down to a more local level like suburb or postcode?
You can, but all of these would be point based (as in, you will just see a point / piechart over the postcode instead of an actual boundary of the suburb / postcode)
I'm having issues with section II) Creating a drill down in map report, step 1) "Select the same set of data in excel, go to the PowerPivot tab and select the Add to Data Model icon as shown below".
I get an error message stating that the table has already been added to the data model. When I try to proceed to Step 2, the Power View report option doesn't appear in the Pivot Table drop down.
Is it only possible to create a Power View report if my data model has multiple tables?
Thank you
No, you should be able to create Power View reports even if you just have one table. Try deleting the existing table in the data model and then try adding it again.
Thanks Much, I played around it very nicely. Good starting point for me to learn.
The only thing i couldn't able to match yours is, not able to see the Power View option inside the PowerPivot window under Home/PivotTable dropdown. Can you tell me what i am doing wrong here?
Thanks Jason. You are the best!
Interesting, I can't find that option in PowerPivot window any more, looks like they removed it. Nevertheless, we can access Power View from the Insert tab
I've got excel2010 w/powerpivot. is this sort of thing possible with it?
hi jason,
do you know if it's now possible?
"…I really wish that the POLYGON and LINES also could be made available…"
thank you!!!
Still not possible. Although Power Map supports custom images now
Hi Jason, is it possible to remove the count of a category in Power Maps
Due to performance concerns, I am only able to plot up to 100 points at once. WHY??!! I need many more!
100 limit sucks. unusable.
[…] since Power View gave the feature for displaying pie charts in map reports (read section III in Creating Maps in Excel 2013 using Power View), a lot of people have been asking whether it is possible to replicate the same in SSRS. You do […]
I am really loving the theme/design of your website. Do you ever run into any browser compatibility issues?
A handful of my blog audience have complained about my
website not working correctly in Explorer but looks great
in Opera. Do you have any ideas to help fix this problem?
Hi, thanks for sharing.
I’m using Microsoft Excel 2016, I found that the “Power View” button under the “Pivot Table” was no more available….
So I’m stuck at that step~~Could you provide me any other solution to complete creating a map?
Nice one.
if you ever get bored, try another bulk geocoder tool called csv2geo.com that will better job if quality is important.