Читать книгу Excel Power Pivot & Power Query For Dummies - Michael Alexander - Страница 3

1  Cover

2  Title Page

3  Copyright

4  Introduction About This Book Foolish Assumptions Icons Used in This Book Beyond the Book Where to Go from Here

5  Part 1: Supercharged Reporting with Power Pivot Chapter 1: Thinking Like a Database Exploring the Limits of Excel and How Databases Help Getting to Know Database Terminology Understanding Relationships Chapter 2: Introducing Power Pivot Understanding the Power Pivot Internal Data Model Linking Excel Tables to Power Pivot Chapter 3: The Pivotal Pivot Table Introducing the Pivot Table Defining the Four Areas of a Pivot Table Creating Your First Pivot Table Customizing Pivot Table Reports Understanding Slicers Creating a Standard Slicer Getting Fancy with Slicer Customizations Controlling Multiple Pivot Tables with One Slicer Creating a Timeline Slicer Chapter 4: Using External Data with Power Pivot Loading Data from Relational Databases Loading Data from Flat Files Loading Data from Other Data Sources Refreshing and Managing External Data Connections Chapter 5: Working Directly with the Internal Data Model Directly Feeding the Internal Data Model Managing Relationships in the Internal Data Model Managing Queries and Connections Creating a New Pivot Table Using the Internal Data Model Filling the Internal Data Model with Multiple External Data Tables Chapter 6: Adding Formulas to Power Pivot Enhancing Power Pivot Data with Calculated Columns Utilizing DAX to Create Calculated Columns Understanding Calculated Measures Free Your Data with Cube Functions Chapter 7: Diving into DAX DAX Language Fundamentals Understanding Filter Context

6  Part 2: Wrangling Data with Power Query Chapter 8: Introducing Power Query Power Query Basics Understanding Column-Level Actions Understanding Table Actions Chapter 9: Power Query Connection Types Importing Data from Files Importing Data from Database Systems Managing Data Source Settings Data Profiling with Power Query Chapter 10: Transforming Your Way to Better Data Completing Common Transformation Tasks Creating Custom Columns Grouping and Aggregating Data Working with Custom Data Types Chapter 11: Making Queries Work Together Reusing Query Steps Understanding the Append Feature Understanding the Merge Feature Understanding Fuzzy Match Chapter 12: Extending Power Query with Custom Functions Creating and Using a Basic Custom Function Creating a Function to Merge Data from Multiple Excel Files Creating Parameter Queries

7  Part 3: The Part of Tens Chapter 13: Ten Ways to Improve Power Pivot Performance Limit the Number of Rows and Columns in Your Data Model Tables Use Views Instead of Tables Avoid Multi-Level Relationships Let the Back-End Database Servers Do the Crunching Beware of Columns with Many Unique Values Limit the Number of Slicers in a Report Create Slicers Only on Dimension Fields Disable the Cross-Filter Behavior for Certain Slicers Use Calculated Measures Instead of Calculated Columns Upgrade to 64-Bit Excel Chapter 14: Ten Tips for Working with Power Query Getting Quick Information from the Queries & Connections Pane Organizing Queries in Groups Selecting Columns in Queries Faster Renaming Query Steps Quickly Creating Reference Tables Viewing Query Dependencies Setting a Default Load Behavior Preventing Automatic Data Type Changes Disabling Privacy Settings to Improve Performance Disabling Relationship Detection

8  Index

9  About the Author

10  Connect with Dummies

11  End User License Agreement