Excel Power Pivot and Power Query For Dummies

Book description

A guide to PowerPivot and Power Query no data cruncher should be without!

Want to familiarize yourself with the rich set of Microsoft Excel tools and reporting capabilities available from PowerPivot and Power Query? Look no further! Excel PowerPivot & Power Query For Dummies shows you how this powerful new set of tools can be leveraged to more effectively source and incorporate 'big data' Business Intelligence and Dashboard reports. You'll discover how PowerPivot 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.

Gone are the days of relatively small amounts of data—today's data environment demands more from business analysts than ever before. Now, with the help of this friendly, hands-on guide, you'll learn to use PowerPivot and Power Query to expand your skill-set from the one-dimensional spreadsheet to new territories, like relational databases, data integration, and multi-dimensional reporting.

  • Demonstrates how Power Query is used to discover, connect to, and import your data
  • Shows you how to use PowerPivot to model data once it's been imported
  • Offers guidance on using these tools to make analyzing data easier
  • Written by a Microsoft MVP in the lighthearted, fun style you've come to expect from the For Dummies brand

If you spend your days analyzing data, Excel PowerPivot & Power Query For Dummies will get you up and running with the rich set of Excel tools and reporting capabilities that will make your life—and work—easier.

Table of contents

    1. Cover
    2. Introduction
      1. About This Book
      2. Foolish Assumptions
      3. How This Book Is Organized
      4. Icons Used In This Book
      5. Beyond the Book
      6. Where to Go from Here
    3. Part I: Supercharged Reporting with Power Pivot
      1. Chapter 1: Thinking Like a Database
        1. Exploring the Limits of Excel and How Databases Help
        2. Getting to Know Database Terminology
        3. Understanding Relationships
      2. Chapter 2: Introducing Power Pivot
        1. Understanding the Power Pivot Internal Data Model
        2. Activating the Power Pivot Add-In
        3. Linking Excel Tables to Power Pivot
      3. Chapter 3: The Pivotal Pivot Table
        1. Introducing the Pivot Table
        2. Defining the Four Areas of a Pivot Table
        3. Creating Your First Pivot Table
        4. Customizing Pivot Table Reports
        5. Understanding Slicers
        6. Creating a Standard Slicer
        7. Getting Fancy with Slicer Customizations
        8. Controlling Multiple Pivot Tables with One Slicer
        9. Creating a Timeline Slicer
      4. Chapter 4: Using External Data with Power Pivot
        1. Loading Data from Relational Databases
        2. Loading Data from Flat Files
        3. Loading Data from Other Data Sources
        4. Refreshing and Managing External Data Connections
      5. Chapter 5: Working Directly with the Internal Data Model
        1. Directly Feeding the Internal Data Model
        2. Adding a New Table to the Internal Data Model
        3. Removing a Table from the Internal Data Model
        4. Creating a New Pivot Table Using the Internal Data Model
        5. Filling the Internal Data Model with Multiple External Data Tables
      6. Chapter 6: Adding Formulas to Power Pivot
        1. Enhancing Power Pivot Data with Calculated Columns
        2. Utilizing DAX to Create Calculated Columns
        3. Understanding Calculated Measures
        4. Free Your Data With Cube Functions
      7. Chapter 7: Publishing Power Pivot to SharePoint
        1. Understanding SharePoint
        2. Understanding Excel Services for SharePoint
        3. Publishing an Excel Workbook to SharePoint
        4. Publishing to a Power Pivot Gallery
    4. Part II: Wrangling Data with Power Query
      1. Chapter 8: Introducing Power Query
        1. Installing and Activating a Power Query Add-In
        2. Power Query Basics
        3. Understanding Column-Level Actions
        4. Understanding Table Actions
      2. Chapter 9: Power Query Connection Types
        1. Importing Data from Files
        2. Importing Data from Database Systems
        3. Managing Data Source Settings
      3. Chapter 10: Transforming Your Way to Better Data
        1. Completing Common Transformation Tasks
        2. Creating Custom Columns
        3. Grouping and Aggregating Data
      4. Chapter 11: Making Queries Work Together
        1. Reusing Query Steps
        2. Understanding the Append Feature
        3. Understanding the Merge Feature
      5. Chapter 12: Extending Power Query with Custom Functions
        1. Creating and Using a Basic Custom Function
        2. Creating a Function to Merge Data from Multiple Excel Files
        3. Creating Parameter Queries
    5. Part III: The Part of Tens
      1. Chapter 13: Ten Ways to Improve Power Pivot Performance
        1. Limit the Number of Rows and Columns in Your Data Model Tables
        2. Use Views Instead of Tables
        3. Avoid Multi-Level Relationships
        4. Let the Back-End Database Servers Do the Crunching
        5. Beware of Columns with Non-Distinct Values
        6. Limit the Number of Slicers in a Report
        7. Create Slicers Only on Dimension Fields
        8. Disable the Cross-Filter Behavior for Certain Slicers
        9. Use Calculated Measures Instead of Calculated Columns
        10. Upgrade to 64-Bit Excel
      2. Chapter 14: Ten Tips for Working with Power Query
        1. Getting Quick Information from the Workbook Queries Pane
        2. Organizing Queries in Groups
        3. Selecting Columns in Queries Faster
        4. Renaming Query Steps
        5. Quickly Creating Reference Tables
        6. Copying Queries to Save Time
        7. Setting a Default Load Behavior
        8. Preventing Automatic Data Type Changes
        9. Disabling Privacy Settings to Improve Performance
        10. Disabling Relationship Detection
    6. About the Author
    7. Cheat Sheet
    8. Advertisement Page
    9. Connect with Dummies
    10. End User License Agreement

Product information

  • Title: Excel Power Pivot and Power Query For Dummies
  • Author(s): Michael Alexander
  • Release date: April 2016
  • Publisher(s): For Dummies
  • ISBN: 9781119210641