Learn to crunch huge amounts of data with PowerPivot and Power Query Do you have a ton of data you need to make sense of? Microsoft’s Excel program can handle amazingly large data sets, but you’ll need to get familiar with PowerPivot and Power Query to get started. And that’s where Dummies comes in. With step-by-step instructions—accompanied by ample screenshots—Excel PowerPivot & Power Query For Dummies will teach you how to save time, simplify your processes, and enhance your data analysis and reporting. Use Power Query to discover, connect to, and import your organization’s data. Then use PowerPivot to model it in Excel. You’ll also learn to: Make use of databases to store large amounts of data Use custom functions to extend and enhance Power Query Add the functionality of formulas to PowerPivot and publish data to SharePoint If you’re expected to wrangle, interpret, and report on large amounts of data, Excel PowerPivot & Power Query For Dummies gives you the tools you need to get up to speed quickly.

Table of Contents:
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