Check out Power Update from Per Solli: http://mrx.cl/powerupd.

THAT'S ALL FOR POWER BI IN THIS BOOK

At the end of October 2016, this is my Power BI story. Things are evolving quickly. New features come out every month. By the time you read this, things have improved.

To learn more about using Power Query, check out the book, M is for Data Monkey by Ken Puls and Miguel

Escobar.

To learn more about the DAX language, check out the book, Power Pivot and Power BI from Rob Collie and Avi Singh.

PUT A PIVOT TABLE ON A MAP IN EXCEL 2013 POWER MAP

Power Map is a new feature in Excel 2013. It was renamed to 3D Maps in Excel 2016. If your data in- cludes fields such as Street Address, City, State, Zip, Country, Continent, Latitude or Longitude, you can plot your pivot table on a map. You can then use the mouse to fly through the map, zooming in on various pockets of your data.

Figure 1043 Analysis of home sales in a neighborhood.

To get started, select any data set that has a geographic component. On the Excel 2013 ribbon, choose

Insert, Power Map. In Excel 2016, use Insert, 3D Maps.

Excel will use Bing to geocode each data point into a location on the map. If your data has obvious headings like City, State, Power Map will figure those out. But if your field names do not make sense, you need to choose a geography level for each field.

Gotcha: if you have a field such as “123 Main Street”, be sure to classify this as a Street and not an Ad- dress. Only use Address if your field is like “1060 W Addison St, Chicago, IL 60613”.

For example, if your data set is ultra-local, down to the point where you are analyzing each house on a street, the default width of the columns is too wide.

You can not see the detail for each house, as each column is as wide as a city block.

To fix the problem, click the Cog Wheel Icon in the Field List. Then, click Layer Options. You will find a Thickness setting. Change this to 10% and you will be able to see data for each house on the street.

Also in this panel, you can control the color for each category, and control if negatives are shown or not.

Another item that needs editing is the box that shows the current date. It usually starts out showing date and time, even though your data might only be at the date level.

Right-click the box, choose Edit. You can now choose a new font, color, and date format.

To replace the map with a satellite image, open the Themes dropdown and choose the second theme.

An icon in the Ribbon lets you add labels to the map. This adds city names, street names, and so on.

CREATING A VIDEO FROM POWER MAP

Power Map will let you tell a story by stringing together a series of scenes. You might show the whole country for 10 seconds, then zoom in to Florida and fly over Florida for a few seconds. Then fly to NYC and hover for a few seconds before flying to Southern California.

When you are finished, you can have Power Map render the tour as a movie. Here are some tips:

Do not put a Date field on your map. If you do, each scene will re-animate the time animation. Perhaps you want to start with a zoomed out view with a time field. Let the map animate. Then, remove the time field and add scenes as you zoom in to each location.

To build a tour, start with the map zoomed out and click Add Scene. Then, zoom in to the first area of interest and click Add Scene.

If you need to explain one point in a scene, right-click and choose Add Annotation. Add the Scene and then remember to remove the annotation before the next scene.

In the Tour Editor pane on the left side, click the first scene.

In the Field List on the right side, click the Cog Wheel, then Scene Options. Add an Effect so you have some motion during the scene. If you need to pause for a longer period at one scene, increase the Scene

Duration. Keeping adding additional scenes and adjusting the duration. Test the tour by clicking Play Tour.

When the tour looks good, choose Create Video. Note that it can take almost an hour to render a short video.