High Impact Data Visualization in Excel with Power View, 3D Maps, Get & Transform and Power BI

618 Pages·2016·29.7 MB·English

by  Adam Aspin (auth.)

High Impact Data
Visualization in Excel
with Power View,
3D Maps, Get &
Transform and
Power BI
Going from data to insight with Power BI
and Microso ’s self-service BI tool set
—
Second Edition
—
Adam Aspin
H igh Impact Data
Visualization in Excel
with Power View, 3D
Maps, Get & Transform
and Power BI
S econd Edition

A dam Aspin
High Impact Data Visualization in Excel with Power View, 3D Maps, Get & Transform and Power BI
Adam Aspin
Staford
United Kingdom
ISBN-13 (pbk): 978-1-4842-2399-4 ISBN-13 (electronic): 978-1-4842-2400-7
DOI 10.1007/978-1-4842-2400-7
Library of Congress Control Number: 2016959188
Copyright © 2016 by Adam Aspin
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the
material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation,
broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage
and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or
hereafter developed.
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol
with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only
in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the
trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are
not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to
proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication,
neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or
omissions that may be made. The publisher makes no warranty, express or implied, with respect to the
material contained herein.
Managing Director: Welmoed Spahr
Lead Editor: Jonathan Gennick
Development Editor: Laura Berendson
Editorial Board: Steve Anglin, Pramila Balan, Laura Berendson, Aaron Black, Louise Corrigan,
Jonathan Gennick, Todd Green, Robert Hutchinson, Celestin Suresh John, Nikhil Karkal,
James Markham, Susan McDermott, Matthew Moodie, Natalie Pao, Gwenan Spearing
Coordinating Editor: Jill Balzano
Copy Editor: Kim Wimpsett
Compositor: SPi Global
Indexer: SPi Global
Artist: SPi Global
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street,
6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail o
F or Hannah
Contents at a Glance
About the Author .................................................................................................xxvii
Acknowledgments ................................................................................................xxix
Introduction ..........................................................................................................xxxi
■ Chapter 1: Self-Service Business Intelligence with Excel 2016 ............................ 1
■ Chapter 2: Power View and Tables ...................................................................... 17
■ Chapter 3: Filtering Data in Power View ............................................................. 57
■ Chapter 4: Charts in Power View ........................................................................ 87
■ Chapter 5: Advanced Charting with Power View ............................................... 121
■ Chapter 6: Interactive Data Selection in Power View ........................................ 143
■ Chapter 7: Images and Presentation in Power View ......................................... 175
■ Chapter 8: Mapping Data in Power View ........................................................... 205
■ Chapter 9: 3D Maps ........................................................................................... 225
■ C hapter 10: Discovering and Loading Data with Get & Transform
in Excel 2016 ..................................................................................................... 279
■ Chapter 11: Transforming Data Sets Using Get & Transform ............................. 321
■ Chapter 12: Data Cleansing with Get & Transform ............................................ 349
■ Chapter 13: Data Mashup with Get & Transform ............................................... 371
■ Chapter 14: Extending the Excel Data Model Using Power Pivot ....................... 411
■ Chapter 15: Extending the Data Model with Calculated Columns ..................... 463
v
■ CONTENTS AT A GLANCE
■ Chapter 16: Adding Measures to the Data Model .............................................. 495
■ Chapter 17: Analyzing Data over Time with DAX ............................................... 529
■ Chapter 18: Self-Service Business Intelligence with PowerBI.com .................. 565
Index ..................................................................................................................... 591
vi
Contents
About the Author .................................................................................................xxvii
Acknowledgments ................................................................................................xxix
Introduction ..........................................................................................................xxxi
■ Chapter 1: Self-Service Business Intelligence with Excel 2016 ............................ 1
The Excel BI Toolkit .......................................................................................................... 2
The Self-Service Business Intelligence Universe in Excel 2016 ............................................................. 2
Get and Transform .................................................................................................................................. 3
Power Pivot ............................................................................................................................................. 3
Power View ....................................................................................................................... 4
3D Maps.................................................................................................................................................. 5
The Power BI Service ............................................................................................................................. 5
Preparing the Excel BI Toolkit ........................................................................................... 5
Enabling the Excel BI Toolkit ................................................................................................................... 6
Power View ............................................................................................................................................. 8
Get and Transform ................................................................................................................................ 10
3D Maps................................................................................................................................................ 11
Corporate BI or Self-Service BI? .................................................................................... 11
The Excel Data Model ..................................................................................................... 12
How This Book Is Designed to Be Read .......................................................................... 13
Discovering Data .................................................................................................................................. 14
Creating a Data Model .......................................................................................................................... 14
Taking Data and Preparing It for Output ............................................................................................... 14
Delivering Geodata ............................................................................................................................... 14
vii
■ CONTENTS
Taking Existing Excel BI and Sharing It ................................................................................................. 14
Delivering Excel BI to Mobile Devices................................................................................................... 14
Learning the Product Suite Following a Real-World Path ..................................................................... 15
Conclusion ...................................................................................................................... 15
■ Chapter 2: Power View and Tables ...................................................................... 17
The Power View Experience ........................................................................................... 18
Adding a Power View Sheet to an Excel Workbook .............................................................................. 18
The Power View Interface ..................................................................................................................... 18
The Power View Ribbon ........................................................................................................................ 19
The Field List ........................................................................................................................................ 21
Using the Field List ............................................................................................................................... 21
Renaming or Deleting a Power View Report ......................................................................................... 23
Tables in Power View ..................................................................................................... 23
Adding a Table ...................................................................................................................................... 23
Deleting a Table .................................................................................................................................... 26
Changing the Table Size and Position ................................................................................................... 26
Changing Column Order ........................................................................................................................ 27
Removing Columns from a Table .......................................................................................................... 27
Types of Data .................................................................................................................. 28
Data and Aggregations ................................................................................................... 29
Enhancing Tables ........................................................................................................... 30
The Design Ribbon ................................................................................................................................ 30
Row Totals ............................................................................................................................................ 32
Formatting Columns of Numbers .......................................................................................................... 33
Default Formatting ................................................................................................................................ 34
Changing Column Widths ...................................................................................................................... 34
Font Sizes in Tables .............................................................................................................................. 36
Copying a Table ..................................................................................................................................... 36
Sorting by Column ................................................................................................................................ 36
Table Granularity ............................................................................................................ 37
viii
■ CONTENTS
Matrix Tables .................................................................................................................. 38
Row Matrix ........................................................................................................................................... 38
Column Matrix ...................................................................................................................................... 40
Sorting Data in Matrix Tables ................................................................................................................ 43
Drilling Through with Matrix Tables ................................................................................ 44
Drilling Down ........................................................................................................................................ 45
Drilling Up ............................................................................................................................................. 46
Reapplying Matrix Visualization ............................................................................................................ 47
Drilling Through with Column Hierarchies ............................................................................................ 47
Card Visualizations ......................................................................................................... 49
Card Visualization Styles ...................................................................................................................... 52
Sorting Data in Card-View Tables ......................................................................................................... 53
Switching Between Table Types ..................................................................................... 53
Key Performance Indicators .......................................................................................... 54
Creating Power View Reports and Tables Without a Data Model .................................... 54
Conclusion ...................................................................................................................... 55
■ Chapter 3: Filtering Data in Power View ............................................................. 57
Filters ............................................................................................................................. 57
View Filters ..................................................................................................................... 59
Adding Filters ....................................................................................................................................... 59
Using the (All) Filter .............................................................................................................................. 61
Clearing Filters ..................................................................................................................................... 61
Deleting Filters ..................................................................................................................................... 62
Expanding and Collapsing Filters ......................................................................................................... 62
Subsetting Large Filter Lists ................................................................................................................. 63
Filtering Different Data Types ......................................................................................... 66
Range Filter Mode ................................................................................................................................ 66
List Filter Mode ..................................................................................................................................... 68
Quickly Excluding Outliers .................................................................................................................... 69
ix
■ CONTENTS
Date and Time Data ........................................................................................................ 69
Other Data Types ............................................................................................................ 71
Multiple Filters ............................................................................................................... 71
Advanced Filters ............................................................................................................. 71
Applying an Advanced Filter ................................................................................................................. 72
Clearing an Advanced Filter .................................................................................................................. 73
Advanced Wildcard Filters .................................................................................................................... 74
Numeric Filters ..................................................................................................................................... 75
Date and Time Filters ..................................................................................................... 76
Complex Filters .............................................................................................................. 78
Advanced Text Filter Options ................................................................................................................ 79
Advanced Numeric Filter Options ......................................................................................................... 79
Advanced Date Filter Options ............................................................................................................... 80
Visualization-Level Filters .............................................................................................. 80
Filter Hierarchy ............................................................................................................... 81
Filtering Tips ................................................................................................................... 83
Don’t Filter Too Soon ............................................................................................................................. 83
Drill-Down and Filters ........................................................................................................................... 84
Annotate, Annotate, Annotate ................................................................................................................ 85
Conclusion ...................................................................................................................... 86
■ Chapter 4: Charts in Power View ........................................................................ 87
A First Chart ................................................................................................................... 87
Deleting a Chart .............................................................................................................. 91
Basic Chart Modifi cation ................................................................................................ 92
Basic Chart Types ........................................................................................................... 93
Column Charts ...................................................................................................................................... 93
Line Charts ........................................................................................................................................... 94
Pie Charts ............................................................................................................................................. 94
Essential Chart Adjustments ................................................................................................................. 96
x