Excel Power Pivot & Power Query For Dummies

Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years’ experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel and has been named Microsoft Excel MVP for his contributions to the Excel community.

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 Functions 119

Chapter 7: Diving into DAX 121

DAX Language Fundamentals 121

Using DAX operators 125

Applying conditional logic in DAX 126

Working with DAX aggregate functions 128

Exploring iterator functions and row context 129

Understanding Filter Context 133

Getting context transitions with the CALCULATE function 135

Adding flexibility with the FILTER function 137

Part 2: Wrangling Data with Power Query 141

Chapter 8: Introducing Power Query 143

Power Query Basics 144

Starting the query 144

Understanding query steps 150

Refreshing Power Query data 152

Managing existing queries 153

Understanding Column-Level Actions 155

Understanding Table Actions 157

Chapter 9: Power Query Connection Types 159

Importing Data from Files 160

Getting data from Excel workbooks 160

Getting data from CSV and text files 161

Getting data from PDF files 163

Getting data from folders 164

Importing Data from Database Systems 165

A connection for every database type 165

Getting data from other data systems 167

Walk-through: Getting data from a database 168

Managing Data Source Settings 170

Data Profiling with Power Query 171

Data Profiling options 172

Data Profiling quick actions 173

Chapter 10: Transforming Your Way to Better Data 175

Completing Common Transformation Tasks 176

Removing duplicate records 176

Filling in blank fields 178

Concatenating columns 179

Changing case 181

Finding and replacing specific text 181

Trimming and cleaning text 183

Extracting the left, right, and middle values 184

Splitting columns using character markers 187

Pivoting and unpivoting fields 189

Creating Custom Columns 193

Concatenating with a custom column 195

Understanding data type conversions 196

Spicing up custom columns with functions 197

Adding conditional logic to custom columns 199

Grouping and Aggregating Data 201

Working with Custom Data Types 203

Chapter 11: Making Queries Work Together 207

Reusing Query Steps 208

Understanding the Append Feature 211

Creating the needed base queries 212

Appending the data 213

Understanding the Merge Feature 216

Understanding Power Query joins 216

Merging queries 217

Understanding Fuzzy Match 221

Chapter 12: Extending Power Query with Custom Functions 225

Creating and Using a Basic Custom Function 225

Creating a Function to Merge Data from Multiple Excel Files 229

Creating Parameter Queries 236

Preparing for a parameter query 236

Creating the base query 238

Creating the parameter query 239

Part 3: The Part of Tens 243

Chapter 13: Ten Ways to Improve Power Pivot Performance 245

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

Use Views Instead of Tables 246

Avoid Multi-Level Relationships 246

Let the Back-End Database Servers Do the Crunching 247

Beware of Columns with Many Unique Values 248

Limit the Number of Slicers in a Report 248

Create Slicers Only on Dimension Fields 249

Disable the Cross-Filter Behavior for Certain Slicers 250

Use Calculated Measures Instead of Calculated Columns 250

Upgrade to 64-Bit Excel 251

Chapter 14: Ten Tips for Working with Power Query 253

Getting Quick Information from the Queries & Connections Pane 253

Organizing Queries in Groups 254

Selecting Columns in Queries Faster 255

Renaming Query Steps 256

Quickly Creating Reference Tables 257

Viewing Query Dependencies 258

Setting a Default Load Behavior 259

Preventing Automatic Data Type Changes 259

Disabling Privacy Settings to Improve Performance 261

Disabling Relationship Detection 261

Index 263