Table of Contents

Introduction 1

About This Book 2

Foolish Assumptions 3

How This Book Is Organized 3

Part I Supercharged Reporting with Power Pivot 3

Part II Wrangling Data with Power Query 4

Part III The Part of Tens 4

Icons Used in This Book 4

Beyond the Book 5

Where to Go from Here 5

Part I Supercharged Reporting with Power Point 7

Chapter 1 Thinking Like a Database 9

Exploring the Limits of Excel and How Databases Help 9

Scalability 9

Transparency of analytical processes 11

Separation of data and presentation 12

Getting to Know Database Terminology 13

Databases 13

Tables 14

Records, fields, and values 14

Queries 15

Understanding Relationships 15

Chapter 2 Introducing Power Point 19

Understanding the Power Pivot Internal Data Model 20

Activating the Power Pivot Add-In 22

Linking Excel Tables to Power Pivot 24

Preparing Excel tables 25

Adding Excel Tables to the data model 26

Creating relationships between Power Pivot tables 27

Managing existing relationships 29

Using the Power Pivot data model in reporting 31

Chapter 3 The Pivotal Pivot Table 33

Introducing the Pivot Table 33

Defining the Four Areas of a Pivot Table 34

Values area 34

Row area 35

Column area 36

Filter area 36

Creating Your First Pivot Table 37

Changing and rearranging a pivot table 40

Adding a report filter 41

Keeping the pivot table fresh 43

Customizing Pivot Table Reports 44

Changing the pivot table layout 44

Customizing field names 46

Applying numeric formats to data fields 47

Changing summary calculations 48

Suppressing subtotals 49

Showing and hiding data items 52

Hiding or showing items without data 53

Sorting the pivot table 56

Understanding Slicers 57

Creating a Standard Sheer 59

Getting Fancy with Sheer Customizations 61

Size and placement 61

Data item columns 62

Miscellaneous slicer settings 63

Controlling Multiple Pivot Tables with One Slicer 63

Creating a Timeline Slicer 64

Chapter 4 Using External Data with Power Pivot 67

Loading Data from Relational Databases 67

Loading data from SQL Server 68

Loading data from Microsoft Access databases 74

Loading data from other relational database systems 76

Loading Data from Flat Files 79

Loading data from external Excel files 79

Loading data from text files 82

Loading data from the Clipboard 84

Loading Data from Other Data Sources 85

Refreshing and Managing External Data Connections 86

Manually refreshing Power Pivot data 87

Setting up automatic refreshing 87

Preventing Refresh All 88

Editing the data connection 89

Chapter 5 Working Directly with the Internal Data Model 93

Directly Feeding the Internal Data Model 93

Adding a New Table to the Internal Data Model 99

Removing a Table from the Internal Data Model 101

Creating a New Pivot Table Using the Internal Data Model 102

Filling the Internal Data Model with Multiple External Data Tables 104

Chapter 6 Adding Formulas to Power Pivot 107

Enhancing Power Pivot Data with Calculated Columns 107

Creating your first calculated column 108

Formatting calculated columns 109

Referencing calculated columns in other calculations 110

Hiding calculated columns from end users 111

Utilizing DAX to Create Calculated Columns 112

Identifying DAX functions that are safe for calculated columns 112

Building DAX-driven calculated columns 114

Referencing fields from other tables 117

Understanding Calculated Measures 119

Creating a calculated measure 120

Editing and deleting calculated measures 122

Free Your Data With Cube Functions 123

Chapter 7 Publishing Power Pivot to SharePoint 125

Understanding SharePoint 125

Understanding Excel Services for SharePoint 127

Publishing an Excel Workbook to SharePoint 128

Publishing to a Power Pivot Gallery 131

Exploring the Power Pivot Gallery 131

Refreshing data connections in published Power Pivot workbooks 132

Part II Wrangling Data with Power Query 135

Chapter 8 Introducing Power Query 137

Installing and Activating a Power Query Add-Sn 138

Power Query Basics 139

Starting the query 140

Understanding query steps 146

Refreshing Power Query data 148

Managing existing queries 149

Understanding Column-Level Actions 151

Understanding Table Actions 153

Chapter 9 Power Query Connection Types 155

Importing Data from Files 156

Getting data from Excel workbooks 156

Getting data from CSV and text files 158

Getting data from XML tiles 160

Getting data from folders 162

Importing Data from Database Systems 163

A connection for every database type 163

Getting data from other data systems 165

Walk-through: Getting data from a database 166

Managing Data Source Settings 168

Chapter 10 Transforming Your Way to Better Data 171

Completing Common Transformation Tasks 172

Removing duplicate records 172

Filling in blank fields 174

Concatenating columns 176

Changing case 178

Finding and replacing specific text 179

Trimming and cleaning text 180

Extracting the left, right, and middle values 181

Splitting columns using character markers 184

Pivoting and unpivoting fields 186

Creating Custom Columns 190

Concatenating with a custom column 192

Understanding data type conversions 193

Spicing up custom columns with functions 194

Adding conditional logic to custom columns 196

Grouping and Aggregating Data 198

Chapter 11 Making Queries Work Together 201

Reusing Query Steps 201

Understanding the Append Feature 205

Creating the needed base queries 205

Appending the data 207

Understanding the Merge Feature 209

Understanding Power Query joins 209

Merging queries 210

Chapter 12 Extending Power Query with Custom Functions 215

Creating and Using a Basic Custom Function 215

Creating a Function to Merge Data from Multiple Excel Files 219

Creating Parameter Queries 225

Preparing for a parameter query 226

Creating the base query 227

Creating the parameter query 229

Part III The Part of Tens 233

Chapter 13 Ten Ways to Improve Power Pivot Performance 235

Limit the Number of Rows and Columns in Your Data Model Tables 236

Use Views Instead of Tables 236

Avoid Multi-Level Relationships 236

Let the Back-End Database Servers Do the Crunching 237

Beware of Columns with Non-Distinct Values 238

Limit the Number of Sheers in a Report 238

Create Slicers Only on Dimension Fields 239

Disable the Cross-Filter Behavior for Certain Slicers 240

Use Calculated Measures Instead of Calculated Columns 240

Upgrade to 64-Bit Excel 241

Chapter 14 Ten Tips for Working with Power Query 243

Getting Quick Information from the Workbook Queries Pane 243

Organizing Queries in Groups 244

Selecting Columns in Queries Faster 245

Renaming Query Steps 246

Quickly Creating Reference Tables 247

Copying Queries to Save Time 248

Setting a Default Load Behavior 249

Preventing Automatic Data Type Changes 250

Disabling Privacy Settings to Improve Performance 251

Disabling Relationship Detection 252

Index 255