Excel Power Pivot & Power Query For Dummies, 2nd Edition

Excel Power Pivot & Power Query For Dummies, 2nd Edition

by Michael Alexander
Released March 2022
Publisher(s): For Dummies
ISBN: 9781119844488

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Learn to crunch huge amounts of data with PowerPivot and Power Query

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.

And that’s where Dummies comes in. With step-by-step instructions—accompanied by ample screenshots—Excel PowerPivot & Power Query For Dummies 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:

  • Make use of databases to store large amounts of data
  • Use custom functions to extend and enhance Power Query
  • Add the functionality of formulas to PowerPivot and publish data to SharePoint

If you’re expected to wrangle, interpret, and report on large amounts of data, Excel PowerPivot & Power Query For Dummies gives you the tools you need to get up to speed quickly.

Table of contents

  1. Cover
  2. Title Page
  3. Copyright
  4. Introduction
    1. About This Book
    2. Foolish Assumptions
    3. Icons Used in This Book
    4. Beyond the Book
    5. Where to Go from Here
  5. Part 1: 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. 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. Managing Relationships in the Internal Data Model
      3. Managing Queries and Connections
      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: Diving into DAX
      1. DAX Language Fundamentals
      2. Understanding Filter Context
  6. Part 2: Wrangling Data with Power Query
    1. Chapter 8: Introducing Power Query
      1. Power Query Basics
      2. Understanding Column-Level Actions
      3. 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
      4. Data Profiling with Power Query
    3. Chapter 10: Transforming Your Way to Better Data
      1. Completing Common Transformation Tasks
      2. Creating Custom Columns
      3. Grouping and Aggregating Data
      4. Working with Custom Data Types
    4. Chapter 11: Making Queries Work Together
      1. Reusing Query Steps
      2. Understanding the Append Feature
      3. Understanding the Merge Feature
      4. Understanding Fuzzy Match
    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
  7. Part 3: 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 Many Unique 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 Queries & Connections Pane
      2. Organizing Queries in Groups
      3. Selecting Columns in Queries Faster
      4. Renaming Query Steps
      5. Quickly Creating Reference Tables
      6. Viewing Query Dependencies
      7. Setting a Default Load Behavior
      8. Preventing Automatic Data Type Changes
      9. Disabling Privacy Settings to Improve Performance
      10. Disabling Relationship Detection
  8. Index
  9. About the Author
  10. Connect with Dummies
  11. End User License Agreement

Product information

  • Title: Excel Power Pivot & Power Query For Dummies, 2nd Edition
  • Author(s): Michael Alexander
  • Release date: March 2022
  • Publisher(s): For Dummies
  • ISBN: 9781119844488