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

Preface....5

Learning Objective....5

Prerequisites....5

Technical Requirements....5

Technological Requirements....6

How I Got Here....7

What Is “Modern Analytics”? Why Excel?....7

Book Overview....8

Part I, Data Cleaning and Transformation with Power Query....9

Part II, Data Modeling and Analysis with Power Pivot....10

Part III, The Excel Data Analytics Toolkit....11

End-of-Chapter Exercises....12

This Is Not a Laundry List....13

Conventions Used in This Book....13

Using Code Examples....15

O’Reilly Online Learning....15

How to Contact Us....16

Acknowledgments....17

I. Data Cleaning and Transformation with Power Query....19

1. Tables: The Portal to Modern Excel....20

Creating and Referring to Table Headers....20

Viewing the Table Footers....22

Naming Excel Tables....25

Formatting Excel Tables....26

Updating Table Ranges....27

Organizing Data for Analytics....27

Conclusion....29

Exercises....29

2. First Steps in Excel Power Query....31

What Is Power Query?....31

Power Query as Excel Myth Buster....31

“Excel Is Not Reproducible”....31

“Excel Does Not Have a True null”....33

“Excel Can’t Process More Than 1,048,576 Rows”....33

Power Query as Excel’s ETL Tool....34

Extract....35

Transform....36

Load....37

A Tour of the Power Query Editor....38

The Ribbon Menu....39

Queries....42

The Imported Data....43

Exiting the Power Query Editor....47

Returning to the Power Query Editor....49

Data Profiling in Power Query....50

What Is Data Profiling?....50

Exploring the Data Preview Options....50

Overriding the Thousand-Row Limit....56

Closing Out of Data Profiling....56

Conclusion....57

Exercises....57

3. Transforming Rows in Power Query....59

Removing the Missing Values....59

Refreshing the Query....63

Splitting Data into Rows....65

Filling in Headers and Cell Values....70

Replacing Column Headers....70

Filling Down Blank Rows....70

Conclusion....71

Exercises....71

4. Transforming Columns in Power Query....73

Changing Column Case....73

Delimiting by Column....75

Changing Data Types....76

Deleting Columns....77

Working with Dates....78

Creating Custom Columns....79

Loading & Inspecting the Data....80

Calculated Columns Versus Measures....81

Reshaping Data....82

Conclusion....84

Exercises....84

5. Merging and Appending Data in Power Query....86

Appending Multiple Sources....86

Connecting to External Excel Workbooks....87

Appending the Queries....90

Understanding Relational Joins....92

Left Outer Join: Think VLOOKUP()....94

Inner Join: Only the Matches....98

Managing Your Queries....100

Grouping Your Queries....101

Viewing Query Dependencies....102

Conclusion....103

Exercises....104

II. Data Modeling and Analysis with Power Pivot....105

6. First Steps in Power Pivot....106

What Is Power Pivot?....106

Why Power Pivot?....106

Power Pivot and the Data Model....110

Loading the Power Pivot Add-in....112

A Brief Tour of the Power Pivot Add-In....114

Data Model....114

Calculations....115

Tables....115

Relationships....116

Settings....116

Conclusion....116

Exercises....116

7. Creating Relational Models in Power Pivot....118

Connecting Data to Power Pivot....118

Creating Relationships....120

Identifying Fact and Dimension Tables....124

Arranging the Diagram View....125

Editing the Relationships....126

Loading the Results to Excel....127

Understanding Cardinality....132

One-to-One Cardinality....133

One-to-Many Relationships....134

Many-to-Many Relationships....134

Why Does Cardinality Matter?....135

Understanding Filter Direction....136

Filtering orders with users....137

Filtering users with orders....139

Filter Direction and Cardinality....141

From Design to Practice in Power Pivot....141

Creating Columns in Power Pivot....141

Calculating in Power Query Versus Power Pivot....142

Example: Calculating Profit Margin....142

Recoding Column Values with SWITCH()....145

Creating and Managing Hierarchies....147

Creating a Hierarchy in Power Pivot....148

Using Hierarchies in the PivotTable....148

Loading the Data Model to Power BI....150

Power BI as the Third Piece of “Modern Excel”....150

Importing the Data Model to Power BI....151

Viewing the Data in Power BI....153

Conclusion....155

Exercises....156

8. Creating Measures and KPIs in Power Pivot....157

Creating DAX Measures....157

Creating Implicit Measures....157

Creating Explicit Measures....160

Creating KPIs....167

Adjusting Icon Styles....171

Adding the KPI to the PivotTable....172

Conclusion....174

Exercises....174

9. Intermediate DAX for Power Pivot....176

CALCULATE() and the Importance of Filter Context....176

CALCULATE() with One Criterion....178

CALCULATE() with Multiple Criteria....179

AND Conditions....179

OR Conditions....180

CALCULATE() with ALL()....181

Time Intelligence Functions....184

Adding a Calendar Table....185

Creating Basic Time Intelligence Measures....187

Conclusion....193

Exercises....194

III. The Excel Data Analytics Toolkit....196

10. Introducing Dynamic Array Functions....197

Dynamic Array Functions Explained....197

What Is an Array in Excel?....198

Array References....198

Array Formulas....200

An Overview of Dynamic Array Functions....203

Finding Distinct and Unique Values with UNIQUE()....203

Finding Unique Versus Distinct Values....205

Using the Spill Operator....206

Filtering Records with FILTER()....207

Adding a Header Column....209

Filtering by Multiple Criteria....210

Sorting Records with SORTBY()....211

Sorting by Multiple Criteria....213

Sorting by Another Column Without Printing It....213

Creating Modern Lookups with XLOOKUP()....214

XLOOKUP() Versus VLOOKUP()....215

A Basic XLOOKUP()....216

XLOOKUP() and Error Handling....217

XLOOKUP() and Looking Up to the Left....218

Other Dynamic Array Functions....219

Dynamic Arrays and Modern Excel....219

Conclusion....220

Exercises....221

11. Augmented Analytics and the Future of Excel....222

The Growing Complexity of Data and Analytics....222

Excel and the Legacy of Self-Service BI....223

Excel for Augmented Analytics....224

Using Analyze Data for AI Powered Insights....224

Building Statistical Models with XLMiner....229

Reading Data from an Image....233

Sentiment Analysis with Azure Machine Learning....236

Conclusion....241

Exercises....241

12. Python with Excel....243

Reader Prerequisites....244

The Role of Python in Modern Excel....244

A Growing Stack Requires Glue....245

Network Effects Mean Faster Development Time....245

Bring Modern Development to Excel....246

Using Python and Excel Together with pandas and openpyxl....248

Other Python Packages for Excel....250

Demonstration of Excel Automation with pandas and openpyxl....251

Cleaning Up the Data in pandas....252

Summarizing Findings with openpyxl....258

Adding a Styled Data Source....265

Conclusion....268

Exercises....269

13. Conclusion and Next Steps....270

Exploring Excel’s Other Features....270

LET() and LAMBDA()....270

Power Automate, Office Scripts, and Excel Online....271

Continued Exploration of Power Query and Power Pivot....273

Power Query and M....273

Power Pivot and DAX....274

Power BI for Dashboards and Reports....275

Azure and Cloud Computing....276

Python Programming....277

Large Language Models and Prompt Engineering....277

Parting Words....278

Index....279

About the Author....318

If you haven't modernized your data cleaning and reporting processes in Microsoft Excel, you're missing out on big productivity gains. And if you're looking to conduct rigorous data analysis, more can be done in Excel than you think. This practical book serves as an introduction to the modern Excel suite of features along with other powerful tools for analytics.

George Mount of Stringfest Analytics shows business analysts, data analysts, and business intelligence specialists how to make bigger gains right from your spreadsheets by using Excel's latest features. You'll learn how to build repeatable data cleaning workflows with Power Query, and design relational data models straight from your workbook with Power Pivot. You'll also explore other exciting new features for analytics, such as dynamic array functions, AI-powered insights, and Python integration.

Learn how to build reports and analyses that were previously difficult or impossible to do in Excel. This book shows you how to:

• Build repeatable data cleaning processes for Excel with Power Query
• Create relational data models and analysis measures with Power Pivot
• Pull data quickly with dynamic arrays
• Use AI to uncover patterns and trends from inside Excel
• Integrate Python functionality with Excel for automated analysis and reporting