Introduction 1 Part I: Supercharged Reporting with Power Pivot 7 Chapter 1: Thinking Like a Database 9 Chapter 2: Introducing Power Pivot 19 Chapter 3: The Pivotal Pivot Table 33 Chapter 4: Using External Data with Power Pivot 67 Chapter 5: Working Directly with the Internal Data Model 93 Chapter 6: Adding Formulas to Power Pivot 107 Chapter 7: Publishing Power Pivot to SharePoint 125 Part II: Wrangling Data with Power Query 135 Chapter 8: Introducing Power Query 137 Chapter 9: Power Query Connection Types 155 Chapter 10: Transforming Your Way to Better Data 171 Chapter 11: Making Queries Work Together 201 Chapter 12: Extending Power Query with Custom Functions 215 Part III: The Part of Tens 233 Chapter 13: Ten Ways to Improve Power Pivot Performance 235 Chapter 14: Ten Tips for Working with Power Query 243 Index 255

Learn to: Extract data from databases and external files for use in Excel reportingCreate powerful interactive reporting mechanisms and dashboardsIntegrate Power Query with Power Pivot to create truly robust Business Intelligence The Excel guide that no serious data cruncher should live without! Want to familiarize yourself with the rich set of tools and reporting capabilities available in Excel? Look no further! Inside, you'll discover how Power Pivot and Power Query not only allow you to save time and simplify your processes, but also enable you to substantially enhance your data analysis and reporting capabilities. Rev up your reporting — fire up Power Pivot, build table relationships, and create robust pivot table reporting using multiple data sourcesFormulate a plan — find out how to add formulas to Power Pivot reports and get the know-how to share your BI solutions with othersWhen in doubt — get the lowdown on Power Query, from choosing where to load Query results to refreshing and managing queries—and everything in betweenCan't we all just get along? — discover how to make Queries work together, build your own Query functions, and make marvelous magic with M Query languageGet what you need — transform your way to better data by adding Query filters, filling in blank spaces and empty values, splitting and merging columns, and transposing and pivoting data Open the book and find: How to create your own database right in ExcelWays to make pivot tables that use multiple data sourcesHow to scrape websites and import hard-to-reach data with Power QueryTricks to simplify and automate data cleanup and transformationHow to create your own powerful formulas and functions to go beyond simple data imports