Refreshing Data

If your data has changed (and remember that it could have changed in a source database or a shared worksheet on a network drive), you could both need and want to refresh the data so that Power Map will redraw the visualization to display the latest version of events. To do this

1. Click the Refresh Data button in the Power Map ribbon.

The data will be updated from the source, and the map(s) will be redrawn. If the path to the source data involves a Power Query process and a PowerPivot data model, then the entire chain of data will be updated.

Geographical Data Types

In this first Power Map example we added a single geographical data field. What is more, this field was recognized instantly for what it was—country names. In the real world of mapping data, however, you may not only have to add several fields but also specify which type of geographical data each field represents. Put simply, Power Map needs to know what the data you are supplying represents. Not only that, it needs to know what it is looking at without ambiguity. Consequently, it is up to you to define the source data as clearly and unambiguously as possible. This can involve one or more of several possible approaches.

Showing and Hiding the Task Panel

If you have finished, temporarily or permanently, with the Task panel, you can hide it (or make it reappear) by clicking the Task Panel button in the Power View ribbon. Alternatively, to make it disappear entirely, you can click the Close icon (the small X) at the top right of the panel.

Task Panel Panes

A Task panel can flip between three main panes:

• The Data pane (geographical and metrics)
• Display settings
• Layer modification

You can move between the available panes by clicking on the icons shown in Figure 14-4.

For the next few pages we will be looking only at the Data pane; I will introduce the others a little further on in this chapter. Since we are looking at the Data pane of the Task panel, you need to know something important—how to switch from geographical data to data for metrics and vice versa.

Whenever you create a new map in Power Map, you begin by adding the geographical data, as we did in the first example, earlier. Fairly logically, Power Map defaults to displaying the geographical data view of the Data pane when you start to build a geographical representation of your data. As you saw in step 2 of the previous example, once the geography data is in place, you switch to the metric Data pane by clicking Next at the bottom of the Task pane. So what do you do to return to the geographical data view should you need to modify this data? The answer is that you click on the Geography icon in the Data pane, which is shown in Figure 14-5:

Clicking on the Geography icon will take you back to the initial task pane from which you started out. Once this view is selected, you can modify the geographical data to suit your requirements. As before, clicking Next will switch to display the data for any metrics that you want to show on the map.

The Task Panel Data Views

You saw the data views of the Task panel briefly in steps 2 and 3 of the earlier example. As this reference was only in passing, here is a more complete overview of what the two views in this pane actually do.

• The Geography view lets you select the source of the geographical data from the data set.
• The Metric view allows you to select which source data metrics will be shown in the map.

Both of these data elements can be taken a lot further than the first example showed. However, I think that it is easier to understand the possibilities using examples, as a result, we will see how to develop the use of both geographical data and display metrics a little further on.

In the meantime, the two views are explained in Figure 14-6.

Removing a Field

Inevitably there will be times when you will want to remove a field from the Task panel. The technique is the same whether you are deleting a field from the Geography view or the Metric view.

1. Click on the popup for the field (or the data type for geographical fields).
2. Select Remove from the popup menu.

The field will be removed from the map (but not from the underlying data model) and the map will be redrawn to reflect the modification. Alternatively, you can uncheck the field in the Field List of the Task panel if you prefer.

Moving Around a Map

Moving around a map (without altering the pitch) is, as you would probably expect, extremely easy:

1. Click on the Pan Right icon (the right facing symbol) to alter the geographical area displayed in the map visualization.

Zooming In or Out

It is conceivable that the map that is displayed is not at a scale that you would prefer. Fortunately this is extremely easy to adjust to get the effect that you want. All you have to do is

1. Click on the Zoom Out button (the minus sign) to zoom out or click on the Zoom In button (the plus sign) to zoom in.

Changing the Pitch of a Map

Power Map lets you change the pitch, or viewing angle, of a map. This can be particularly useful when displaying complex column charts, for example. All you have to do is

1. Press Alt and drag the map.

Flat Map and 3-D Globe

Although the Power Map default is to show a three dimensional globe, you can flip to a flattened view if you prefer. This view will show more data and will allow you to avoid having parts of the map disappear over the horizon. To switch between 3-D and flat mapping:

1. Click the Flat Map button in the Power Map ribbon.

Going to a Specific Location

Sometimes all you want to do is move to a particular location. You can do this in a couple of ways. If you can see the place where you want to take a closer look, simply double-click on it and Power Map will zoom in. If you want to jump straight to a particular region or town, then

1. Click Find Location in the Power Map ribbon. The Find Location dialog will appear.
2. Enter the place you want to find. The Find Location dialog will look like Figure 14-8.

   

   Figure 14-8. The Find Location dialog

3. Click Find. Power Map will jump to the location.
4. If Power Map has found the place that you were looking for, you can close the Find Location dialog by clicking on the Close icon (the X at the top right). If not, enter a new location and click Find, again.

Bubble Maps

A very simple, yet effective, way of displaying data is to use a bubble map. This is simply a circular representation of the data for each data point, where the relative size of each dot (or bubble, or point; there are many synonyms used to describe this) gives an idea of the proportional extent of the underlying data.

Bubble maps in Power Map come in two distinct flavors:

• Simple bubbles.
• Bubbles with multiple “subdivisions” or categories. These look like pie charts superimposed upon the map.

Let’s look at each in turn. To begin with, here is how to create a simple bubble map:

1. Starting with the PowerMapSample.xlsx file, click Map in the Insert tab to launch Power Map.
2. Select the CountryName field from the Countries table and click Next.
3. Select CostPrice from the SalesData Table.
4. Remove the Legend by clicking on the close icon (the small X) at the top right of the legend.
5. Select Bubble as the type. Your map should look like Figure 14-10.

   

   Figure 14-10. A simple bubble map

Now let’s extend this by adding a further breakdown—and consequently further information—by adding categories to the map.

1. In the bubble map you just created, click Legend in the ribbon to add a legend.
2. Resize and place the legend where it does not hide any data.
3. In the Task panel, click on the Make field in the SalesData table. Make will be added to the CATEGORY box.
4. Hover the cursor over one of the segments for the pie in the United Kingdom. A popup will appear explaining what the segment represents.

The map should now look like the one in Figure 14-11. As you can see, each bubble is now a pie chart.

I have a couple of comments to make here. First, you can only add a single category to a map. So, if you need multiple categories (such as make and model) to subdivide the data further, then you need to add a calculated column to the data first. Second, the aesthetics of having a legend or not will entirely depend on your requirements and circumstances.

An alternative to analysis by sector in a pie-style bubble map is to compare multiple values. As an example, let’s suppose that you want to see the proportional costs (Parts, Labour, and Discount) for each country.

1. Starting with the PowerMapSample.xlsx file, click Map in the Insert tab to launch Power Map.
2. Select the CountryName field from the Countries table and click Next.
3. Select the following fields from the SalesData table:
   1. SpareParts
   2. LabourCost
   3. TotalDiscount
4. Select Bubble as the type.
5. Click Flat Map in the Power Map ribbon to remove the 3-D view.
6. Resize and reposition the legend.
7. To anticipate a little (and to ring the changes), click the Themes button and select the first theme on the second row. The map should look like Figure 14-12.

I will explain the use of themes in more detail a little later on in this chapter. Nonetheless, I wanted to add a little variety to the presentation. As you can see, you have displayed the costs, and their proportional representation, for each country.

Column Maps

To take the example a little further, but still using very simple data, let’s now see how you can use columns to display the data for each geographical element. Here again there are two main options:

• Clustered columns
• Stacked columns

In both cases the height of the column represents the scale of the data. In the case of clustered columns, you can see the data points side by side; in the case of stacked columns, you can get an idea not only of the relative values but also of the totals for each geographical point.

Heat Maps

The term heat map can mean many different things to different people. In Power Map it means a colored bubble where the intensity and shading of the color represents the scale of values that is represented. To see this, let’s look at the costs of sales for UK towns.

1. Follow steps 1 to 7 that you used to create a stacked column map.
2. Select Heat Map as the type from the TYPE popup.
3. Adjust the zoom, panning, and pitch to get the best effect, once again centering the map on the United Kingdom.

The map should look similar to Figure 14-15.

Region Maps

The first map that we created in this chapter was a region map, and the example that you created covers much of what you can do when you use this map type. Nonetheless, there is an interesting variation on a theme that deserves to be explained—using categories with region maps.

Suppose, for instance, that you want to see the sales by country, but you also want the colors of car sold to shape the data. Here is how:

1. Starting with the PowerMapSample.xlsx file, click Map in the Insert tab to launch Power Map.
2. Select the CountryName field from the Countries table and click Next.
3. Select SalePrice from the SalesData Table.
4. Drag the Colour field from the Colours table to the CATEGORY box.
5. Delete the legend.
6. Select Region as the type from the TYPE popup.
7. Adjust the zoom and panning to get the best effect.
8. Click on Spain to highlight the borders of the country.
9. Hover the cursor over any country to display the sales breakdown by color of car sold. The map should look something like Figure 14-16.

   

   Figure 14-16. A region map with categories applied

When you see the result in a black and white book, the effect is probably less immediate. On screen, however, the change is profound as each country is now a different color. If you look at the popup for each country, you will see that the top selling category for each country has forced the choice of color for the country. In a couple of pages, you will see how to choose these colors so that you can, for this example, set the display color to match the sale color.

As a final remark, you can only select a region for which data exists.

The Settings View

The first thing to retain is that all the map options that I listed earlier are adjusted from the Settings view of the Task panel. As a result, you will have to make sure that the Task panel is visible before you can proceed. The second point is that the settings will vary slightly depending on the type of map that you have chosen. So, as an example, let’s suppose that I want to display a heat map of sales of spare parts by country, but I also want to make the heat “bubbles” larger and alter the shading. Here is how:

1. Starting with the PowerMapSample.xlsx file, click Map in the Insert tab to launch Power Map.
2. Select the CountryName field from the Countries table.
3. Select the Town field from the Clients table.
4. Click Next.
5. Select CostPrice from the SalesData Table.
6. Resize and reposition the legend.
7. Select Heat Map as the type from the TYPE popup.
8. Adjust the zoom and panning to get the best effect, focusing on Europe.
9. Click on the Settings icon in the Task panel (this icon is shown in Figure 14-4).
10. Set the Colour Scale to 125% (or drag the slider to the right to get the effect that you need).
11. Set the Radius Of Influence to 500% (or drag the slider to the right to get the effect that you need). The Task panel should look like Figure 14-17.

    

    Figure 14-17. The Task Panel Settings view

The map should look like Figure 14-18.

Now that you have seen the principles, it is probably easiest to outline the options for each map type rather than give examples for each variation on a theme. As there are certain common options, I will begin with those first. Any shared settings are given in Table 14-5.

Text Boxes

A picture (or even a map) may be worth many words, but on occasion, you still need a few comments to make a point. Consequently (or inevitably) you can add one or more text boxes to your Power Map visualizations to drive home the message. So, let’s suppose you’re the national Sales Manager for the UK and you want to crow about your success. Here is how you might add a text box to Figure 14-14.

1. Once you have created the Power Map which displays the data, click the Text Box button in the Power Map ribbon. The Edit Text Box dialog will appear.
2. Click inside the TITLE box and enter a title. I will add UK Sales Key To Success! in this example.
3. Click inside the DESCRIPTION box and enter a description. I will use More Sales Of More Makes To More Customers! as an example.
4. Click inside the TITLE box and select the font, font size, and font attributes that you want to apply. I will choose Tahoma, 20pt, bold.
5. Click inside the DESCRIPTION box and select the font, font size, and font attributes that you want to apply. I will choose Andalus, 14, italic. The content preview box will display the text as it will appear. The Edit Text Box dialog should look like Figure 14-21.

   

   Figure 14-21. The Edit Text Box dialog

6. Click OK. The Edit Text Box dialog will close, and the text box will appear on the map.
7. Reposition the text box by dragging its title bar (the colored bar that appears when the pointer is placed over the text box).
8. Resize the text box by dragging its resize handles (the circles that appear at the top left and bottom right when the pointer is placed over the text box).
9. Click on the map to deselect the text box. The text box might look something like Figure 14-22.

   

   Figure 14-22. A text box in Power Map

You can see this text box used in a map in Figure 14-24, a little further on in this chapter. To alter the properties of a text box, all you have to do is right-click on a text box and select Edit from the context menu. The Edit Text Box dialog will be displayed, and you can alter the text of the title and/or the description as well as modify the font attributes.

To remove a text box you can

• Right-click on a text box and select Remove from the context menu.
• Place the cursor over the Text Box so that the title bar and resize handles appear and then click the Close icon in the top right corner of the text box.

The various options available for text boxes are explained in Table 14-11.

Adding a Timeline

Rather than add a timeline to an existing map, I prefer to re-create a map so that you can see the whole process. What you do is

1. Starting with the PowerMapSample.xlsx file, click Map in the Insert tab to launch Power Map.
2. Select the CountryName field from the Countries table.
3. Select the Town field from the Clients table.
4. Click Next.
5. Select CostPrice from the SalesData Table.
6. Resize and reposition the legend.
7. Select Column as the type from the TYPE popup.
8. Add Make to the CATEGORY.
9. Click the Stacked Column icon above the CATEGORY box.
10. Expand the Date Table, and check the MonthAndYearAbbr field. This will be added to the TIME box in the Task panel. The timeline slider and a Time Decorator will appear on the map.
11. Adjust the zoom and panning to get the best effect—focus on the United Kingdom.
12. Right-click on the Time Decorator and select Edit. The Edit Time Decorator dialog will appear.
13. Select June 2010 (the month and year format) from the Time Format popup. The Edit Time Decorator dialog will look like Figure 14-23.

    

    Figure 14-23. The Edit Time Decorator dialog

14. Click Accept to close the Edit Time Decorator dialog.
15. Add the text box described in the previous section.
16. Resize and reposition the Time Decorator just as you learned to do with a Text Box. The map should look something like Figure 14-24.

    

    Figure 14-24. A map with a timeline added

And that is it. You have a map that can display the evolution of your data over time. Interestingly, when you add a timeline, the map does not change; this is because a timeline’s default setting is to show the data at the end of the time period. Now, in the next section, we will see how you can use the timeline to enhance your presentation.

Using a Timeline

So, what can we do now that your map has a timeline, and what does it bring to the party? Perhaps the first thing is to understand the timeline itself. Figure 14-25 explains the various parts of a timeline.

2-D Charts

In some cases you may want to enhance a purely geographical representation of data with a more classic visualization. Power Map can let you do this, without distracting from its core aim, by letting you add a two-dimensional chart to a map. This chart will apply to an existing layer of that map and will use the data that the selected layer already represents on the map.

As an example, when shading is used, such as in the case of Region maps, I find that a chart can give a more accurate grasp of the underlying figures. So, let’s see how to add a 2-D chart to a map that contains two layers:

• A Region map of sales by country
• A Stacked Bar map of sales by town and make

Here is the way to go about creating such a visualization:

1. Follow steps 1-8 and 15-22 from the procedure in the “Using Layers” section to create a two-layer map of
   1. sales by country
   2. sales by town and make
2. Select the SalesByCountry layer.
3. Click the 2-D Chart button in the Power Map ribbon. A chart will be added.
4. Select the Horizontal chart type from the popup list of available chart types at the right of the chart name.
5. Resize the chart and position it so that the country data is not obscured.

The map should now look like Figure 14-30.

2-D Chart Types

There are only four available 2-D charts:

• Column charts
• Stacked column charts
• Bar charts
• Stacked bar charts

As these chart types are identical to the Power View charts described in Chapter 4, I will not describe them again here.

Hiding the Map Data

If you really do not want the data to appear in the map at all, but you do want it to show in a chart, then there is a trick that will allow this. It is a fudge, I admit, but a useful one!

1. Create the map shown in Figure 14-30.
2. Select the SalesByMake layer, and click the Settings icon.
3. Click the Color popup for the CostPrice (Sum) field.
4. Click More Colors. The Color palette will appear.
5. Select a light shade of gray.
6. Click OK. This shade will appear in both the chart and as the shading for the countries on the map. The result should look like Figure 14-31.

   

   Figure 14-31. Hiding map data

The trick here is to find a shade (it need not be gray) that will scarcely show a difference on the map but will be visible in the 2-D chart.

Creating Power Map Tours

To see this in action, we will create (or re-create) a couple of independent maps in a single Excel workbook and see how to manage these elements:

1. Starting with the sample file PowerMapSample.xlsx, create the map you first made for Figure 14-3 at the start of this chapter.
2. Assuming that the Tour Editor is visible on the left (and if it is not, click the Tour Editor button in the Power Map ribbon to display it), click inside the TOUR NAME box and enter a name. I suggest SalesByRegion.
3. Click on FILE Close to close Power Map.
4. From inside Excel, click Map in the Insert ribbon. The Launch Power Map dialog will be displayed, as shown in Figure 14-32.

   

   Figure 14-32. The Launch Power Map dialog

5. Click the New Tour icon (the plus sign in a circle). A new, blank map will be created.
6. Create the map shown in Figure 14-10.
7. Name this tour GrossProfit.
8. Click on FILE Close to close Power Map.

That is it. You now have two separate maps inside the Excel workbook. Whenever you click the Map button in the Excel Insert ribbon, you will see the existing tours. You can launch a tour directly simply by clicking on the preview of the tour which is visible in the Launch Power Map dialog.

Deleting a Power Map Tour

Sometimes you will want to delete a tour. This is how to do so:

1. From inside Excel, click Map in the Insert ribbon; the Launch Power Map dialog will be displayed.
2. Hover the pointer over one of the tours. An X will appear to the upper right of the map image, as shown in Figure 14-33.

   

   Figure 14-33. Deleting a tour

3. Click on the X. Excel will display a confirmation dialog as in Figure 14-34.

   

   Figure 14-34. Confirm tour deletion

4. Click OK. The tour will be deleted.

Transitions

When creating this example, you tried out a couple of transitions. A transition is always the visual effect that introduces a scene—and that will link a scene to its predecessor. The available transitions are explained in Table 14-13. You can alter the speed of a transition using the Effect Speed slider.

Exporting a Movie

Once you have created and perfected a movie, you are not limited to using Power Map to display your artwork. You can export a Power Map movie as a Windows multimedia file (in MP4 format) so that it can be played back without a user needing Excel 2013 or Office 356. To create a movie file:

1. Create a movie as described earlier.
2. Click Create Video from the Power Map toolbar. The Create Video dialog will be displayed, as shown in Figure 14-35.

   

   Figure 14-35. The Create Video dialog

3. Select the appropriate resolution as a function of the output device that you expect to be used.
4. Click Create. The Save Movie dialog will appear.
5. Enter a file name, select a destination directory, and click Save.
6. The Creating Video message box will appear as in Figure 14-36.

   

   Figure 14-36. Exporting a Power Map movie

7. Click Close.

You can now open the Power Map movie file in a multimedia player—or you can double-click on it in Windows Explorer to play back the movie. A version of the movie described here can be found at C:HighImpactDataVisualizationWithPowerBISampleMovie.Mp4, assuming that you have downloaded the sample files from the Apress web site.