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

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 Microsoft ’s self-service BI tool set
—
Second Edition
—
Adam Aspin
High Impact Data
Visualization in Excel
with Power View, 3D
Maps, Get & Transform
and Power BI
Second Edition

Adam Aspin
High Impact Data Visualization in Excel with Power View, 3D Maps, Get & Transform and Power BI
Adam Aspin
Stafford
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 [email protected] ,
or visit w ww.springer.com . Apress Media, LLC is a California LLC and the sole member (owner) is Springer
Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.
For information on translations, please e-mail [email protected] , or visit w ww.apress.com .
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our Special
Bulk Sales–eBook Licensing web page at w ww.apress.com/bulk-sales .
Any source code or other supplementary materials referenced by the author in this text are available to
readers at w ww.apress.com . For detailed information about how to locate your book’s source code, go to
www.apress.com/source-code/ . Readers can also access source code at SpringerLink in the Supplementary
Material section for each chapter.
Printed on acid-free paper
For 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
■ Chapter 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