Modern Data Analytics in Excel: Using Power Query, Power Pivot, and More for Enhanced Data Analytics

Preface
   Learning Objective
   Prerequisites
      Technical Requirements
      Technological Requirements
   How I Got Here
   What Is “Modern Analytics”? Why Excel?
   Book Overview
      Part I, Data Cleaning and Transformation with Power Query
      Part II, Data Modeling and Analysis with Power Pivot
      Part III, The Excel Data Analytics Toolkit
   End-of-Chapter Exercises
   This Is Not a Laundry List
   Conventions Used in This Book
   Using Code Examples
   O’Reilly Online Learning
   How to Contact Us
   Acknowledgments
I. Data Cleaning and Transformation with Power Query
1. Tables: The Portal to Modern Excel
   Creating and Referring to Table Headers
   Viewing the Table Footers
   Naming Excel Tables
   Formatting Excel Tables
   Updating Table Ranges
   Organizing Data for Analytics
   Conclusion
   Exercises
2. First Steps in Excel Power Query
   What Is Power Query?
   Power Query as Excel Myth Buster
      “Excel Is Not Reproducible”
      “Excel Does Not Have a True null”
      “Excel Can’t Process More Than 1,048,576 Rows”
   Power Query as Excel’s ETL Tool
      Extract
      Transform
      Load
   A Tour of the Power Query Editor
      The Ribbon Menu
      Queries
      The Imported Data
      Exiting the Power Query Editor
      Returning to the Power Query Editor
   Data Profiling in Power Query
      What Is Data Profiling?
      Exploring the Data Preview Options
         “Monospaced” and “Show whitespace”
         The “Column quality” and “Column distribution”
            What is a valid cell?
            Missing values
            Cell errors
            Column profile
      Overriding the Thousand-Row Limit
      Closing Out of Data Profiling
   Conclusion
   Exercises
3. Transforming Rows in Power Query
   Removing the Missing Values
   Refreshing the Query
   Splitting Data into Rows
   Filling in Headers and Cell Values
      Replacing Column Headers
      Filling Down Blank Rows
   Conclusion
   Exercises
4. Transforming Columns in Power Query
   Changing Column Case
   Delimiting by Column
   Changing Data Types
   Deleting Columns
   Working with Dates
   Creating Custom Columns
      Loading & Inspecting the Data
      Calculated Columns Versus Measures
   Reshaping Data
   Conclusion
   Exercises
5. Merging and Appending Data in Power Query
   Appending Multiple Sources
      Connecting to External Excel Workbooks
      Appending the Queries
   Understanding Relational Joins
      Left Outer Join: Think VLOOKUP()
      Inner Join: Only the Matches
   Managing Your Queries
      Grouping Your Queries
      Viewing Query Dependencies
   Conclusion
   Exercises
II. Data Modeling and Analysis with Power Pivot
6. First Steps in Power Pivot
   What Is Power Pivot?
   Why Power Pivot?
   Power Pivot and the Data Model
   Loading the Power Pivot Add-in
   A Brief Tour of the Power Pivot Add-In
      Data Model
      Calculations
      Tables
      Relationships
      Settings
   Conclusion
   Exercises
7. Creating Relational Models in Power Pivot
   Connecting Data to Power Pivot
   Creating Relationships
   Identifying Fact and Dimension Tables
      Arranging the Diagram View
      Editing the Relationships
   Loading the Results to Excel
   Understanding Cardinality
      One-to-One Cardinality
      One-to-Many Relationships
      Many-to-Many Relationships
      Why Does Cardinality Matter?
   Understanding Filter Direction
      Filtering orders with users
      Filtering users with orders
      Filter Direction and Cardinality
      From Design to Practice in Power Pivot
   Creating Columns in Power Pivot
      Calculating in Power Query Versus Power Pivot
      Example: Calculating Profit Margin
      Recoding Column Values with SWITCH()
   Creating and Managing Hierarchies
      Creating a Hierarchy in Power Pivot
      Using Hierarchies in the PivotTable
   Loading the Data Model to Power BI
      Power BI as the Third Piece of “Modern Excel”
      Importing the Data Model to Power BI
      Viewing the Data in Power BI
   Conclusion
   Exercises
8. Creating Measures and KPIs in Power Pivot
   Creating DAX Measures
      Creating Implicit Measures
      Creating Explicit Measures
   Creating KPIs
      Adjusting Icon Styles
      Adding the KPI to the PivotTable
   Conclusion
   Exercises
9. Intermediate DAX for Power Pivot
   CALCULATE() and the Importance of Filter Context
   CALCULATE() with One Criterion
   CALCULATE() with Multiple Criteria
      AND Conditions
      OR Conditions
   CALCULATE() with ALL()
   Time Intelligence Functions
      Adding a Calendar Table
      Creating Basic Time Intelligence Measures
   Conclusion
   Exercises
III. The Excel Data Analytics Toolkit
10. Introducing Dynamic Array Functions
   Dynamic Array Functions Explained
      What Is an Array in Excel?
      Array References
         Static array references
         Dynamic array references
      Array Formulas
         Static array formulas
         Dynamic array functions
   An Overview of Dynamic Array Functions
      Finding Distinct and Unique Values with UNIQUE()
      Finding Unique Versus Distinct Values
      Using the Spill Operator
   Filtering Records with FILTER()
      Adding a Header Column
      Filtering by Multiple Criteria
         AND criteria
         OR criteria
         Nested AND/OR criteria
   Sorting Records with SORTBY()
      Sorting by Multiple Criteria
      Sorting by Another Column Without Printing It
   Creating Modern Lookups with XLOOKUP()
      XLOOKUP() Versus VLOOKUP()
      A Basic XLOOKUP()
      XLOOKUP() and Error Handling
      XLOOKUP() and Looking Up to the Left
   Other Dynamic Array Functions
   Dynamic Arrays and Modern Excel
   Conclusion
   Exercises
11. Augmented Analytics and the Future of Excel
   The Growing Complexity of Data and Analytics
   Excel and the Legacy of Self-Service BI
   Excel for Augmented Analytics
   Using Analyze Data for AI Powered Insights
   Building Statistical Models with XLMiner
   Reading Data from an Image
   Sentiment Analysis with Azure Machine Learning
   Conclusion
   Exercises
12. Python with Excel
   Reader Prerequisites
   The Role of Python in Modern Excel
      A Growing Stack Requires Glue
      Network Effects Mean Faster Development Time
      Bring Modern Development to Excel
         Unit testing
         Version control
         Package development and distribution
      Using Python and Excel Together with pandas and openpyxl
         Why pandas for Excel?
         The limitations of working with pandas for Excel
         What openpyxl contributes
         How to use openpyxl with pandas
      Other Python Packages for Excel
   Demonstration of Excel Automation with pandas and openpyxl
      Cleaning Up the Data in pandas
         Working with the metadata
         Pattern matching/regular expressions
         Analyzing missing values
         Creating a percentile
      Summarizing Findings with openpyxl
         Creating a summary worksheet
         Inserting charts
            Option A: Create a native Excel plot
            Option B: Insert a Python image
         Excel versus Python charts
      Adding a Styled Data Source
         Formatting percentages
            Converting to a table
            Applying conditional formatting
            Auto-fitting column widths
   Conclusion
   Exercises
13. Conclusion and Next Steps
   Exploring Excel’s Other Features
      LET() and LAMBDA()
      Power Automate, Office Scripts, and Excel Online
   Continued Exploration of Power Query and Power Pivot
      Power Query and M
      Power Pivot and DAX
      Power BI for Dashboards and Reports
   Azure and Cloud Computing
   Python Programming
   Large Language Models and Prompt Engineering
   Parting Words
Index