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