Introduction 1
About This Book 2
Foolish Assumptions 3
Icons Used in This Book 3
Beyond the Book 4
Where to Go from Here 4
Part 1: Supercharged Reporting with Power Pivot 5
Chapter 1: Thinking Like a Database 7
Exploring the Limits of Excel and How Databases Help 7
Scalability 8
Transparency of analytical processes 9
Separation of data and presentation 10
Getting to Know Database Terminology 11
Databases 11
Tables 11
Records, fields, and values 12
Queries 13
Understanding Relationships 13
Chapter 2: Introducing Power Pivot 17
Understanding the Power Pivot Internal Data Model 18
Linking Excel Tables to Power Pivot 20
Preparing Excel tables 21
Adding Excel Tables to the data model 22
Creating relationships between Power Pivot tables 24
Managing existing relationships 26
Using the Power Pivot data model in reporting 27
Chapter 3: The Pivotal Pivot Table 29
Introducing the Pivot Table 30
Defining the Four Areas of a Pivot Table 30
Values area 30
Row area 31
Column area 31
Filter area 32
Creating Your First Pivot Table 33
Changing and rearranging a pivot table 36
Adding a report filter 37
Keeping the pivot table fresh 38
Customizing Pivot Table Reports 40
Changing the pivot table layout 40
Customizing field names 41
Applying numeric formats to data fields 42
Changing summary calculations 43
Suppressing subtotals 44
Showing and hiding data items 47
Hiding or showing items without data 49
Sorting the pivot table 51
Understanding Slicers 52
Creating a Standard Slicer 54
Getting Fancy with Slicer Customizations 56
Size and placement 56
Data item columns 57
Miscellaneous slicer settings 58
Controlling Multiple Pivot Tables with One Slicer 58
Creating a Timeline Slicer 59
Chapter 4: Using External Data with Power Pivot 63
Loading Data from Relational Databases 64
Loading data from SQL Server 64
Loading data from Microsoft Access databases 70
Loading data from other relational database systems 72
Loading Data from Flat Files 75
Loading data from external Excel files 76
Loading data from text files 78
Loading data from the Clipboard 81
Loading Data from Other Data Sources 82
Refreshing and Managing External Data Connections 83
Manually refreshing Power Pivot data 83
Setting up automatic refreshing 84
Preventing Refresh All 85
Editing the data connection 86
Chapter 5: Working Directly with the Internal Data Model 89
Directly Feeding the Internal Data Model 89
Managing Relationships in the Internal Data Model 95
Managing Queries and Connections 96
Creating a New Pivot Table Using the Internal Data Model 97
Filling the Internal Data Model with Multiple External Data Tables 98
Chapter 6: Adding Formulas to Power Pivot 103
Enhancing Power Pivot Data with Calculated Columns 103
Creating your first calculated column 104
Formatting calculated columns 105
Referencing calculated columns in other calculations 106
Hiding calculated columns from end users 107
Utilizing DAX to Create Calculated Columns 108
Identifying DAX functions that are safe for calculated columns 108
Building DAX-driven calculated columns 110
Month sorting in Power Pivot-driven pivot tables 112
Referencing fields from other tables 113
Nesting functions 115
Understanding Calculated Measures 116
Creating a calculated measure 116
Editing and deleting calculated measures 118
Free Your Data with Cube Fu
