{"product_id":"excel-power-pivot-power-query-for-dummies-isbn-9781119844488","title":"Excel Power Pivot \u0026 Power Query For Dummies","description":"\u003cp\u003e\u003cb\u003eLearn to crunch huge amounts of data with PowerPivot and Power Query\u003c\/b\u003e  \u003c\/p\u003e\u003cp\u003eDo 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.  \u003c\/p\u003e\u003cp\u003eAnd that’s where Dummies comes in. With step-by-step instructions—accompanied by ample screenshots—\u003ci\u003eExcel PowerPivot \u0026amp; Power Query For Dummies \u003c\/i\u003ewill 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:  \u003c\/p\u003e\u003cul\u003e   \u003cli\u003eMake use of databases to store large amounts of data\u003c\/li\u003e   \u003cli\u003eUse custom functions to extend and enhance Power Query\u003c\/li\u003e   \u003cli\u003eAdd the functionality of formulas to PowerPivot and publish data to SharePoint\u003c\/li\u003e  \u003c\/ul\u003e  \u003cp\u003eIf you’re expected to wrangle, interpret, and report on large amounts of data, \u003ci\u003eExcel PowerPivot \u0026amp; Power Query For Dummies\u003c\/i\u003e gives you the tools you need to get up to speed quickly. \u003c\/p\u003e\u003cp\u003e\u003cb\u003eIntroduction\u003c\/b\u003e \u003cb\u003e1\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eAbout This Book 2\u003c\/p\u003e \u003cp\u003eFoolish Assumptions 3\u003c\/p\u003e \u003cp\u003eIcons Used in This Book 3\u003c\/p\u003e \u003cp\u003eBeyond the Book 4\u003c\/p\u003e \u003cp\u003eWhere to Go from Here 4\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart 1: Supercharged Reporting with Power Pivot 5\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 1: Thinking Like a Database 7\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eExploring the Limits of Excel and How Databases Help 7\u003c\/p\u003e \u003cp\u003eScalability 8\u003c\/p\u003e \u003cp\u003eTransparency of analytical processes 9\u003c\/p\u003e \u003cp\u003eSeparation of data and presentation 10\u003c\/p\u003e \u003cp\u003eGetting to Know Database Terminology 11\u003c\/p\u003e \u003cp\u003eDatabases 11\u003c\/p\u003e \u003cp\u003eTables 11\u003c\/p\u003e \u003cp\u003eRecords, fields, and values 12\u003c\/p\u003e \u003cp\u003eQueries 13\u003c\/p\u003e \u003cp\u003eUnderstanding Relationships 13\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 2: Introducing Power Pivot 17\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eUnderstanding the Power Pivot Internal Data Model 18\u003c\/p\u003e \u003cp\u003eLinking Excel Tables to Power Pivot 20\u003c\/p\u003e \u003cp\u003ePreparing Excel tables 21\u003c\/p\u003e \u003cp\u003eAdding Excel Tables to the data model 22\u003c\/p\u003e \u003cp\u003eCreating relationships between Power Pivot tables 24\u003c\/p\u003e \u003cp\u003eManaging existing relationships 26\u003c\/p\u003e \u003cp\u003eUsing the Power Pivot data model in reporting 27\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 3: The Pivotal Pivot Table 29\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroducing the Pivot Table 30\u003c\/p\u003e \u003cp\u003eDefining the Four Areas of a Pivot Table 30\u003c\/p\u003e \u003cp\u003eValues area 30\u003c\/p\u003e \u003cp\u003eRow area 31\u003c\/p\u003e \u003cp\u003eColumn area 31\u003c\/p\u003e \u003cp\u003eFilter area 32\u003c\/p\u003e \u003cp\u003eCreating Your First Pivot Table 33\u003c\/p\u003e \u003cp\u003eChanging and rearranging a pivot table 36\u003c\/p\u003e \u003cp\u003eAdding a report filter 37\u003c\/p\u003e \u003cp\u003eKeeping the pivot table fresh 38\u003c\/p\u003e \u003cp\u003eCustomizing Pivot Table Reports 40\u003c\/p\u003e \u003cp\u003eChanging the pivot table layout 40\u003c\/p\u003e \u003cp\u003eCustomizing field names 41\u003c\/p\u003e \u003cp\u003eApplying numeric formats to data fields 42\u003c\/p\u003e \u003cp\u003eChanging summary calculations 43\u003c\/p\u003e \u003cp\u003eSuppressing subtotals 44\u003c\/p\u003e \u003cp\u003eShowing and hiding data items 47\u003c\/p\u003e \u003cp\u003eHiding or showing items without data 49\u003c\/p\u003e \u003cp\u003eSorting the pivot table 51\u003c\/p\u003e \u003cp\u003eUnderstanding Slicers 52\u003c\/p\u003e \u003cp\u003eCreating a Standard Slicer 54\u003c\/p\u003e \u003cp\u003eGetting Fancy with Slicer Customizations 56\u003c\/p\u003e \u003cp\u003eSize and placement 56\u003c\/p\u003e \u003cp\u003eData item columns 57\u003c\/p\u003e \u003cp\u003eMiscellaneous slicer settings 58\u003c\/p\u003e \u003cp\u003eControlling Multiple Pivot Tables with One Slicer 58\u003c\/p\u003e \u003cp\u003eCreating a Timeline Slicer 59\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 4: Using External Data with Power Pivot 63\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eLoading Data from Relational Databases 64\u003c\/p\u003e \u003cp\u003eLoading data from SQL Server 64\u003c\/p\u003e \u003cp\u003eLoading data from Microsoft Access databases 70\u003c\/p\u003e \u003cp\u003eLoading data from other relational database systems 72\u003c\/p\u003e \u003cp\u003eLoading Data from Flat Files 75\u003c\/p\u003e \u003cp\u003eLoading data from external Excel files 76\u003c\/p\u003e \u003cp\u003eLoading data from text files 78\u003c\/p\u003e \u003cp\u003eLoading data from the Clipboard 81\u003c\/p\u003e \u003cp\u003eLoading Data from Other Data Sources 82\u003c\/p\u003e \u003cp\u003eRefreshing and Managing External Data Connections 83\u003c\/p\u003e \u003cp\u003eManually refreshing Power Pivot data 83\u003c\/p\u003e \u003cp\u003eSetting up automatic refreshing 84\u003c\/p\u003e \u003cp\u003ePreventing Refresh All 85\u003c\/p\u003e \u003cp\u003eEditing the data connection 86\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 5: Working Directly with the Internal Data Model 89\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eDirectly Feeding the Internal Data Model 89\u003c\/p\u003e \u003cp\u003eManaging Relationships in the Internal Data Model 95\u003c\/p\u003e \u003cp\u003eManaging Queries and Connections 96\u003c\/p\u003e \u003cp\u003eCreating a New Pivot Table Using the Internal Data Model 97\u003c\/p\u003e \u003cp\u003eFilling the Internal Data Model with Multiple External Data Tables 98\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 6: Adding Formulas to Power Pivot 103\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eEnhancing Power Pivot Data with Calculated Columns 103\u003c\/p\u003e \u003cp\u003eCreating your first calculated column 104\u003c\/p\u003e \u003cp\u003eFormatting calculated columns 105\u003c\/p\u003e \u003cp\u003eReferencing calculated columns in other calculations 106\u003c\/p\u003e \u003cp\u003eHiding calculated columns from end users 107\u003c\/p\u003e \u003cp\u003eUtilizing DAX to Create Calculated Columns 108\u003c\/p\u003e \u003cp\u003eIdentifying DAX functions that are safe for calculated columns 108\u003c\/p\u003e \u003cp\u003eBuilding DAX-driven calculated columns 110\u003c\/p\u003e \u003cp\u003eMonth sorting in Power Pivot–driven pivot tables 112\u003c\/p\u003e \u003cp\u003eReferencing fields from other tables 113\u003c\/p\u003e \u003cp\u003eNesting functions 115\u003c\/p\u003e \u003cp\u003eUnderstanding Calculated Measures 116\u003c\/p\u003e \u003cp\u003eCreating a calculated measure 116\u003c\/p\u003e \u003cp\u003eEditing and deleting calculated measures 118\u003c\/p\u003e \u003cp\u003eFree Your Data with Cube Functions 119\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 7: Diving into DAX 121\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eDAX Language Fundamentals 121\u003c\/p\u003e \u003cp\u003eUsing DAX operators 125\u003c\/p\u003e \u003cp\u003eApplying conditional logic in DAX 126\u003c\/p\u003e \u003cp\u003eWorking with DAX aggregate functions 128\u003c\/p\u003e \u003cp\u003eExploring iterator functions and row context 129\u003c\/p\u003e \u003cp\u003eUnderstanding Filter Context 133\u003c\/p\u003e \u003cp\u003eGetting context transitions with the CALCULATE function 135\u003c\/p\u003e \u003cp\u003eAdding flexibility with the FILTER function 137\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart 2: Wrangling Data with Power Query 141\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 8: Introducing Power Query 143\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003ePower Query Basics 144\u003c\/p\u003e \u003cp\u003eStarting the query 144\u003c\/p\u003e \u003cp\u003eUnderstanding query steps 150\u003c\/p\u003e \u003cp\u003eRefreshing Power Query data 152\u003c\/p\u003e \u003cp\u003eManaging existing queries 153\u003c\/p\u003e \u003cp\u003eUnderstanding Column-Level Actions 155\u003c\/p\u003e \u003cp\u003eUnderstanding Table Actions 157\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 9: Power Query Connection Types 159\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eImporting Data from Files 160\u003c\/p\u003e \u003cp\u003eGetting data from Excel workbooks 160\u003c\/p\u003e \u003cp\u003eGetting data from CSV and text files 161\u003c\/p\u003e \u003cp\u003eGetting data from PDF files 163\u003c\/p\u003e \u003cp\u003eGetting data from folders 164\u003c\/p\u003e \u003cp\u003eImporting Data from Database Systems 165\u003c\/p\u003e \u003cp\u003eA connection for every database type 165\u003c\/p\u003e \u003cp\u003eGetting data from other data systems 167\u003c\/p\u003e \u003cp\u003eWalk-through: Getting data from a database 168\u003c\/p\u003e \u003cp\u003eManaging Data Source Settings 170\u003c\/p\u003e \u003cp\u003eData Profiling with Power Query 171\u003c\/p\u003e \u003cp\u003eData Profiling options 172\u003c\/p\u003e \u003cp\u003eData Profiling quick actions 173\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 10: Transforming Your Way to Better Data 175\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eCompleting Common Transformation Tasks 176\u003c\/p\u003e \u003cp\u003eRemoving duplicate records 176\u003c\/p\u003e \u003cp\u003eFilling in blank fields 178\u003c\/p\u003e \u003cp\u003eConcatenating columns 179\u003c\/p\u003e \u003cp\u003eChanging case 181\u003c\/p\u003e \u003cp\u003eFinding and replacing specific text 181\u003c\/p\u003e \u003cp\u003eTrimming and cleaning text 183\u003c\/p\u003e \u003cp\u003eExtracting the left, right, and middle values 184\u003c\/p\u003e \u003cp\u003eSplitting columns using character markers 187\u003c\/p\u003e \u003cp\u003ePivoting and unpivoting fields 189\u003c\/p\u003e \u003cp\u003eCreating Custom Columns 193\u003c\/p\u003e \u003cp\u003eConcatenating with a custom column 195\u003c\/p\u003e \u003cp\u003eUnderstanding data type conversions 196\u003c\/p\u003e \u003cp\u003eSpicing up custom columns with functions 197\u003c\/p\u003e \u003cp\u003eAdding conditional logic to custom columns 199\u003c\/p\u003e \u003cp\u003eGrouping and Aggregating Data 201\u003c\/p\u003e \u003cp\u003eWorking with Custom Data Types 203\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 11: Making Queries Work Together 207\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eReusing Query Steps 208\u003c\/p\u003e \u003cp\u003eUnderstanding the Append Feature 211\u003c\/p\u003e \u003cp\u003eCreating the needed base queries 212\u003c\/p\u003e \u003cp\u003eAppending the data 213\u003c\/p\u003e \u003cp\u003eUnderstanding the Merge Feature 216\u003c\/p\u003e \u003cp\u003eUnderstanding Power Query joins 216\u003c\/p\u003e \u003cp\u003eMerging queries 217\u003c\/p\u003e \u003cp\u003eUnderstanding Fuzzy Match 221\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 12: Extending Power Query with Custom Functions 225\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eCreating and Using a Basic Custom Function 225\u003c\/p\u003e \u003cp\u003eCreating a Function to Merge Data from Multiple Excel Files 229\u003c\/p\u003e \u003cp\u003eCreating Parameter Queries 236\u003c\/p\u003e \u003cp\u003ePreparing for a parameter query 236\u003c\/p\u003e \u003cp\u003eCreating the base query 238\u003c\/p\u003e \u003cp\u003eCreating the parameter query 239\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart 3: The Part of Tens 243\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 13: Ten Ways to Improve Power Pivot Performance 245\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eLimit the Number of Rows and Columns in Your Data Model Tables 246\u003c\/p\u003e \u003cp\u003eUse Views Instead of Tables 246\u003c\/p\u003e \u003cp\u003eAvoid Multi-Level Relationships 246\u003c\/p\u003e \u003cp\u003eLet the Back-End Database Servers Do the Crunching 247\u003c\/p\u003e \u003cp\u003eBeware of Columns with Many Unique Values 248\u003c\/p\u003e \u003cp\u003eLimit the Number of Slicers in a Report 248\u003c\/p\u003e \u003cp\u003eCreate Slicers Only on Dimension Fields 249\u003c\/p\u003e \u003cp\u003eDisable the Cross-Filter Behavior for Certain Slicers 250\u003c\/p\u003e \u003cp\u003eUse Calculated Measures Instead of Calculated Columns 250\u003c\/p\u003e \u003cp\u003eUpgrade to 64-Bit Excel 251\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 14: Ten Tips for Working with Power Query 253\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eGetting Quick Information from the Queries \u0026amp; Connections Pane 253\u003c\/p\u003e \u003cp\u003eOrganizing Queries in Groups 254\u003c\/p\u003e \u003cp\u003eSelecting Columns in Queries Faster 255\u003c\/p\u003e \u003cp\u003eRenaming Query Steps 256\u003c\/p\u003e \u003cp\u003eQuickly Creating Reference Tables 257\u003c\/p\u003e \u003cp\u003eViewing Query Dependencies 258\u003c\/p\u003e \u003cp\u003eSetting a Default Load Behavior 259\u003c\/p\u003e \u003cp\u003ePreventing Automatic Data Type Changes 259\u003c\/p\u003e \u003cp\u003eDisabling Privacy Settings to Improve Performance 261\u003c\/p\u003e \u003cp\u003eDisabling Relationship Detection 261\u003c\/p\u003e \u003cp\u003eIndex 263 \u003c\/p\u003e \u003cp\u003e\u003cb\u003eMichael Alexander\u003c\/b\u003e 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.\u003c\/p\u003e  \u003cp\u003e\u003cb\u003ePut your data to work with Power Pivot and Power Query\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eAre you looking for powerful new ways to explore, transform, and draw practical intelligence from your organization’s data? \u003ci\u003eExcel\u003csup\u003e®\u003c\/sup\u003e Power Pivot \u0026amp; Power Query For Dummies\u003c\/i\u003e shows you how to save time, simplify your business processes, and improve your analyses. From connecting to large databases to using custom functions and formulas, you’ll discover how to wrangle, interpret, and report on data from multiple data sources in a single unified data model. The step-by-step guides and easy-to-follow advice in this book are fully updated for the 2021 version of Microsoft Excel. \u003c\/p\u003e\u003cp\u003e\u003cb\u003eInside… \u003cul\u003e\n\u003cli\u003eAutomate and simplify your daily data wrangling\u003c\/li\u003e \u003cli\u003eScrape websites and import hard-to-reach information\u003c\/li\u003e \u003cli\u003eManage impossible amounts of data from external databases directly in Excel\u003c\/li\u003e \u003cli\u003eCreate robust pivot table reporting from multiple data sources\u003c\/li\u003e \u003cli\u003eCreate your own formulas and functions to go beyond simple data imports\u003c\/li\u003e\n\u003c\/ul\u003e\u003c\/b\u003e\u003c\/p\u003e","brand":"For Dummies","offers":[{"title":"Default Title","offer_id":47989182234853,"sku":"NP9781119844488","price":39.99,"currency_code":"USD","in_stock":false}],"thumbnail_url":"\/\/cdn.shopify.com\/s\/files\/1\/1842\/7735\/files\/9781119844488.jpg?v=1761783117","url":"https:\/\/k12savings.com\/products\/excel-power-pivot-power-query-for-dummies-isbn-9781119844488","provider":"K12savings","version":"1.0","type":"link"}