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 1About This Book 2Foolish Assumptions 3Icons Used in This Book 3Beyond the Book 4Where to Go from Here 4Part 1: Supercharged Reporting with Power Pivot 5Chapter 1: Thinking Like a Database 7Exploring the Limits of Excel and How Databases Help 7Scalability 8Transparency of analytical processes 9Separation of data and presentation 10Getting to Know Database Terminology 11Databases 11Tables 11Records, fields, and values 12Queries 13Understanding Relationships 13Chapter 2: Introducing Power Pivot 17Understanding the Power Pivot Internal Data Model 18Linking Excel Tables to Power Pivot 20Preparing Excel tables 21Adding Excel Tables to the data model 22Creating relationships between Power Pivot tables 24Managing existing relationships 26Using the Power Pivot data model in reporting 27Chapter 3: The Pivotal Pivot Table 29Introducing the Pivot Table 30Defining the Four Areas of a Pivot Table 30Values area 30Row area 31Column area 31Filter area 32Creating Your First Pivot Table 33Changing and rearranging a pivot table 36Adding a report filter 37Keeping the pivot table fresh 38Customizing Pivot Table Reports 40Changing the pivot table layout 40Customizing field names 41Applying numeric formats to data fields 42Changing summary calculations 43Suppressing subtotals 44Showing and hiding data items 47Hiding or showing items without data 49Sorting the pivot table 51Understanding Slicers 52Creating a Standard Slicer 54Getting Fancy with Slicer Customizations 56Size and placement 56Data item columns 57Miscellaneous slicer settings 58Controlling Multiple Pivot Tables with One Slicer 58Creating a Timeline Slicer 59Chapter 4: Using External Data with Power Pivot 63Loading Data from Relational Databases 64Loading data from SQL Server 64Loading data from Microsoft Access databases 70Loading data from other relational database systems 72Loading Data from Flat Files 75Loading data from external Excel files 76Loading data from text files 78Loading data from the Clipboard 81Loading Data from Other Data Sources 82Refreshing and Managing External Data Connections 83Manually refreshing Power Pivot data 83Setting up automatic refreshing 84Preventing Refresh All 85Editing the data connection 86Chapter 5: Working Directly with the Internal Data Model 89Directly Feeding the Internal Data Model 89Managing Relationships in the Internal Data Model 95Managing Queries and Connections 96Creating a New Pivot Table Using the Internal Data Model 97Filling the Internal Data Model with Multiple External Data Tables 98Chapter 6: Adding Formulas to Power Pivot 103Enhancing Power Pivot Data with Calculated Columns 103Creating your first calculated column 104Formatting calculated columns 105Referencing calculated columns in other calculations 106Hiding calculated columns from end users 107Utilizing DAX to Create Calculated Columns 108Identifying DAX functions that are safe for calculated columns 108Building DAX-driven calculated columns 110Month sorting in Power Pivot–driven pivot tables 112Referencing fields from other tables 113Nesting functions 115Understanding Calculated Measures 116Creating a calculated measure 116Editing and deleting calculated measures 118Free Your Data with Cube Functions 119Chapter 7: Diving into DAX 121DAX Language Fundamentals 121Using DAX operators 125Applying conditional logic in DAX 126Working with DAX aggregate functions 128Exploring iterator functions and row context 129Understanding Filter Context 133Getting context transitions with the CALCULATE function 135Adding flexibility with the FILTER function 137Part 2: Wrangling Data with Power Query 141Chapter 8: Introducing Power Query 143Power Query Basics 144Starting the query 144Understanding query steps 150Refreshing Power Query data 152Managing existing queries 153Understanding Column-Level Actions 155Understanding Table Actions 157Chapter 9: Power Query Connection Types 159Importing Data from Files 160Getting data from Excel workbooks 160Getting data from CSV and text files 161Getting data from PDF files 163Getting data from folders 164Importing Data from Database Systems 165A connection for every database type 165Getting data from other data systems 167Walk-through: Getting data from a database 168Managing Data Source Settings 170Data Profiling with Power Query 171Data Profiling options 172Data Profiling quick actions 173Chapter 10: Transforming Your Way to Better Data 175Completing Common Transformation Tasks 176Removing duplicate records 176Filling in blank fields 178Concatenating columns 179Changing case 181Finding and replacing specific text 181Trimming and cleaning text 183Extracting the left, right, and middle values 184Splitting columns using character markers 187Pivoting and unpivoting fields 189Creating Custom Columns 193Concatenating with a custom column 195Understanding data type conversions 196Spicing up custom columns with functions 197Adding conditional logic to custom columns 199Grouping and Aggregating Data 201Working with Custom Data Types 203Chapter 11: Making Queries Work Together 207Reusing Query Steps 208Understanding the Append Feature 211Creating the needed base queries 212Appending the data 213Understanding the Merge Feature 216Understanding Power Query joins 216Merging queries 217Understanding Fuzzy Match 221Chapter 12: Extending Power Query with Custom Functions 225Creating and Using a Basic Custom Function 225Creating a Function to Merge Data from Multiple Excel Files 229Creating Parameter Queries 236Preparing for a parameter query 236Creating the base query 238Creating the parameter query 239Part 3: The Part of Tens 243Chapter 13: Ten Ways to Improve Power Pivot Performance 245Limit the Number of Rows and Columns in Your Data Model Tables 246Use Views Instead of Tables 246Avoid Multi-Level Relationships 246Let the Back-End Database Servers Do the Crunching 247Beware of Columns with Many Unique Values 248Limit the Number of Slicers in a Report 248Create Slicers Only on Dimension Fields 249Disable the Cross-Filter Behavior for Certain Slicers 250Use Calculated Measures Instead of Calculated Columns 250Upgrade to 64-Bit Excel 251Chapter 14: Ten Tips for Working with Power Query 253Getting Quick Information from the Queries & Connections Pane 253Organizing Queries in Groups 254Selecting Columns in Queries Faster 255Renaming Query Steps 256Quickly Creating Reference Tables 257Viewing Query Dependencies 258Setting a Default Load Behavior 259Preventing Automatic Data Type Changes 259Disabling Privacy Settings to Improve Performance 261Disabling Relationship Detection 261Index 263