Details

Excel Power Pivot & Power Query For Dummies


Excel Power Pivot & Power Query For Dummies


2. Aufl.

von: Michael Alexander

26,99 €

Verlag: Wiley
Format: EPUB
Veröffentl.: 25.01.2022
ISBN/EAN: 9781119844501
Sprache: englisch
Anzahl Seiten: 288

DRM-geschütztes eBook, Sie benötigen z.B. Adobe Digital Editions und eine Adobe ID zum Lesen.

Beschreibungen

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

Diese Produkte könnten Sie auch interessieren:

AutoCAD 2013 For Dummies
AutoCAD 2013 For Dummies
von: Bill Fane, David Byrnes
PDF ebook
23,99 €
IBM Workplace Services Express For Dummies
IBM Workplace Services Express For Dummies
von: Stephen R. Londergan, Michael Loria
PDF ebook
16,99 €
Building Flash Web Sites For Dummies
Building Flash Web Sites For Dummies
von: Doug Sahlin
PDF ebook
18,99 €