Pages

Wednesday, July 18, 2012

Creating Maps in Excel 2013 using Power View

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.

Creating Maps in Excel 2013 using Power View

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.

select data in excel

You should get a loading screen while it takes a couple of seconds to open Power View

loading 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

Map in Power View

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.

enable content in Power View

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.

Basic map report in Power View

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.

map layout properties

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.

powerpivot

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.

Add to data model

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.

PivotTable-->Power View

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.

setting reporting properties

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

map icon besides fields

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.

Adding drilldown fields to location

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.

return to 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

Adding more data in linked table

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

power view report showing new data

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.

Add city to color

2) You can hover the mouse on the pie charts, and the pie chart will expand and show the tooltips as shown below

Pie charts in Power View

You can also use the horizontal / vertical multiples location areas to split it by the selected field as shown below

multiples in Power View

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.

Power View report by latitude and longitude

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.

16 comments:

  1. Fantastic! The map feature is truly useful, especially the ability to use it at latitude and longitude level.

    Julie

    ReplyDelete
  2. 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

    ReplyDelete
  3. @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.

    ReplyDelete
  4. 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

    ReplyDelete
  5. could we change the marker icon?

    ReplyDelete
    Replies
    1. Not possible (atleast out of the box).

      Delete
  6. Wow what an amazing new feature

    Question though, Can you drill down to a more local level like suburb or postcode?

    ReplyDelete
    Replies
    1. 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)

      Delete
  7. 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

    ReplyDelete
    Replies
    1. 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.

      Delete
  8. 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!

    ReplyDelete
    Replies
    1. 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

      Delete
  9. I've got excel2010 w/powerpivot. is this sort of thing possible with it?

    ReplyDelete
  10. 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!!!

    ReplyDelete
    Replies
    1. Still not possible. Although Power Map supports custom images now

      Delete
  11. Hi Jason, is it possible to remove the count of a category in Power Maps

    ReplyDelete