Modern Data Analytics in Excel Using Power Query, Power Pivot, and More for Enhanced Data Analytics (George Mount)（Z-Library）

📄 File Format: PDF

💾 File Size: 19.3 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

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

📄 Page 2

DATA SCIENCE Modern Data Analytics in Excel linkedin.com/company/oreilly-media youtube.com/oreillymedia 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 provides 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 new features for analytics, including 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. • 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 array functions • Use AI to uncover patterns and trends from inside Excel • Integrate Python functionality with Excel for automated analysis and reporting George Mount is the founder of Stringfest Analytics, a consulting firm specializing in analytics professional development. He has worked with leading bootcamps, learning platforms, and practice organizations to help individuals excel at analytics. George is the recipient of Microsoft’s Most Valuable Professional (MVP) award for exceptional technical expertise and community advocacy in Excel, and he’s the author of Advancing into Analytics: From Excel to Python and R (O’Reilly, 2021). He resides in Cleveland, Ohio. 9 7 8 1 0 9 8 1 4 8 8 2 9 5 5 9 9 9 US $59.99 CAN $74.99 ISBN: 978-1-098-14882-9 “ As someone who used Excel occasionally for work, I found Modern Data Analytics easy to follow and packed with practical tips. George Mount’s straightforward approach is helpful for seasoned analysts and casual users alike.” — Meghan Finley Technical writer and editor

📄 Page 3

George Mount Modern Data Analytics in Excel Using Power Query, Power Pivot, and More for Enhanced Data Analytics Boston Farnham Sebastopol TokyoBeijing

📄 Page 4

978-1-098-14882-9 [LSI] Modern Data Analytics in Excel by George Mount Copyright © 2024 Candid World Consulting, LLC. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: Michelle Smith Development Editor: Sara Hunter Production Editor: Christopher Faucher Copyeditor: Penelope Perkins Proofreader: Helena Stirling Indexer: BIM Creatives, LLC Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Kate Dullea May 2024: First Edition Revision History for the First Edition 2024-04-26: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781098148829 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Modern Data Analytics in Excel, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the author and do not represent the publisher’s views. While the publisher and the author have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the author disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights.

📄 Page 5

Table of Contents Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Part I. Data Cleaning and Transformation with Power Query 1. Tables: The Portal to Modern Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Creating and Referring to Table Headers 3 Viewing the Table Footers 6 Naming Excel Tables 8 Formatting Excel Tables 9 Updating Table Ranges 9 Organizing Data for Analytics 10 Conclusion 11 Exercises 11 2. First Steps in Excel Power Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 What Is Power Query? 13 Power Query as Excel Myth Buster 13 “Excel Is Not Reproducible” 13 “Excel Does Not Have a True null” 14 “Excel Can’t Process More Than 1,048,576 Rows” 15 Power Query as Excel’s ETL Tool 15 Extract 15 Transform 17 Load 18 A Tour of the Power Query Editor 18 The Ribbon Menu 19 Queries 21 iii

📄 Page 6

The Imported Data 22 Exiting the Power Query Editor 24 Returning to the Power Query Editor 26 Data Profiling in Power Query 26 What Is Data Profiling? 27 Exploring the Data Preview Options 27 Overriding the Thousand-Row Limit 31 Closing Out of Data Profiling 31 Conclusion 32 Exercises 32 3. Transforming Rows in Power Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Removing the Missing Values 34 Refreshing the Query 37 Splitting Data into Rows 39 Filling in Headers and Cell Values 42 Replacing Column Headers 42 Filling Down Blank Rows 43 Conclusion 44 Exercises 44 4. Transforming Columns in Power Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Changing Column Case 45 Delimiting by Column 47 Changing Data Types 47 Deleting Columns 48 Working with Dates 48 Creating Custom Columns 49 Loading & Inspecting the Data 51 Calculated Columns Versus Measures 52 Reshaping Data 53 Conclusion 54 Exercises 55 5. Merging and Appending Data in Power Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 Appending Multiple Sources 57 Connecting to External Excel Workbooks 58 Appending the Queries 61 Understanding Relational Joins 62 Left Outer Join: Think VLOOKUP() 64 Inner Join: Only the Matches 68 Managing Your Queries 70 iv | Table of Contents

📄 Page 7

Grouping Your Queries 70 Viewing Query Dependencies 71 Conclusion 72 Exercises 73 Part II. Data Modeling and Analysis with Power Pivot 6. First Steps in Power Pivot. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 What Is Power Pivot? 77 Why Power Pivot? 77 Power Pivot and the Data Model 80 Loading the Power Pivot Add-in 81 A Brief Tour of the Power Pivot Add-In 83 Data Model 83 Calculations 83 Tables 84 Relationships 84 Settings 84 Conclusion 84 Exercises 85 7. Creating Relational Models in Power Pivot. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Connecting Data to Power Pivot 87 Creating Relationships 88 Identifying Fact and Dimension Tables 92 Arranging the Diagram View 93 Editing the Relationships 94 Loading the Results to Excel 95 Understanding Cardinality 99 One-to-One Cardinality 100 One-to-Many Relationships 101 Many-to-Many Relationships 101 Why Does Cardinality Matter? 102 Understanding Filter Direction 103 Filtering orders with users 104 Filtering users with orders 105 Filter Direction and Cardinality 106 From Design to Practice in Power Pivot 106 Creating Columns in Power Pivot 106 Calculating in Power Query Versus Power Pivot 106 Example: Calculating Profit Margin 107 Table of Contents | v

📄 Page 8

Recoding Column Values with SWITCH() 109 Creating and Managing Hierarchies 111 Creating a Hierarchy in Power Pivot 111 Using Hierarchies in the PivotTable 112 Loading the Data Model to Power BI 113 Power BI as the Third Piece of “Modern Excel” 113 Importing the Data Model to Power BI 114 Viewing the Data in Power BI 116 Conclusion 118 Exercises 118 8. Creating Measures and KPIs in Power Pivot. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 Creating DAX Measures 119 Creating Implicit Measures 119 Creating Explicit Measures 122 Creating KPIs 128 Adjusting Icon Styles 130 Adding the KPI to the PivotTable 131 Conclusion 132 Exercises 132 9. Intermediate DAX for Power Pivot. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 CALCULATE() and the Importance of Filter Context 134 CALCULATE() with One Criterion 135 CALCULATE() with Multiple Criteria 136 AND Conditions 136 OR Conditions 137 CALCULATE() with ALL() 138 Time Intelligence Functions 140 Adding a Calendar Table 140 Creating Basic Time Intelligence Measures 143 Conclusion 149 Exercises 149 Part III. The Excel Data Analytics Toolkit 10. Introducing Dynamic Array Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 Dynamic Array Functions Explained 153 What Is an Array in Excel? 154 Array References 154 Array Formulas 156 vi | Table of Contents

📄 Page 9

An Overview of Dynamic Array Functions 158 Finding Distinct and Unique Values with UNIQUE() 158 Finding Unique Versus Distinct Values 159 Using the Spill Operator 160 Filtering Records with FILTER() 160 Adding a Header Column 162 Filtering by Multiple Criteria 162 Sorting Records with SORTBY() 163 Sorting by Multiple Criteria 164 Sorting by Another Column Without Printing It 164 Creating Modern Lookups with XLOOKUP() 165 XLOOKUP() Versus VLOOKUP() 165 A Basic XLOOKUP() 166 XLOOKUP() and Error Handling 167 XLOOKUP() and Looking Up to the Left 168 Other Dynamic Array Functions 168 Dynamic Arrays and Modern Excel 169 Conclusion 169 Exercises 170 11. Augmented Analytics and the Future of Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 The Growing Complexity of Data and Analytics 171 Excel and the Legacy of Self-Service BI 172 Excel for Augmented Analytics 173 Using Analyze Data for AI Powered Insights 173 Building Statistical Models with XLMiner 179 Reading Data from an Image 181 Sentiment Analysis with Azure Machine Learning 184 Conclusion 188 Exercises 188 12. Python with Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Reader Prerequisites 190 The Role of Python in Modern Excel 190 A Growing Stack Requires Glue 190 Network Effects Mean Faster Development Time 191 Bring Modern Development to Excel 192 Using Python and Excel Together with pandas and openpyxl 193 Other Python Packages for Excel 194 Demonstration of Excel Automation with pandas and openpyxl 195 Cleaning Up the Data in pandas 196 Summarizing Findings with openpyxl 200 Table of Contents | vii

📄 Page 10

Adding a Styled Data Source 204 Conclusion 206 Exercises 207 13. Conclusion and Next Steps. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 Exploring Excel’s Other Features 209 LET() and LAMBDA() 210 Power Automate, Office Scripts, and Excel Online 210 Continued Exploration of Power Query and Power Pivot 211 Power Query and M 211 Power Pivot and DAX 212 Power BI for Dashboards and Reports 213 Azure and Cloud Computing 213 Python Programming 214 Large Language Models and Prompt Engineering 214 Parting Words 215 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 viii | Table of Contents

📄 Page 11

Preface Welcome to the Excel revolution. By updating how you think about and use Excel, you can unlock significant productivity gains and use your data more powerfully. This book introduces the “modern Excel” suite of features and other powerful analyt‐ ics tools. Learning Objective By the end of this book, you should be able to use modern Excel tools for data clean‐ ing, analysis, reporting, and advanced analytics. In particular, you’ll clean and trans‐ form data with Power Query, create relational models in Power Pivot to build sophisticated analyses, and explore the Excel analytics toolkit to further automate and enhance your work. Prerequisites To meet these objectives, this book makes some technical and technological assumptions. Technical Requirements To make the most of this book, it is recommended that you have a Windows com‐ puter with the Microsoft 365 version of Excel for desktop. The features covered in this book are relatively new and may not be available in older Excel versions. Please note that many of these tools are still being developed for Mac, and compatibility may vary. Due to the fast-paced nature of Excel’s development, it is difficult to provide a precise list of what’s available for each version. Chapter 7 of the book briefly explains how to load a Data Model from Excel into Power BI. It assumes that, as a Microsoft 365 for Windows user, you already have the free version of Power BI Desktop installed on your computer. Chapter 12 delves into ix

📄 Page 12

the integration of Python with Excel, guiding you through the process of download‐ ing Python for free. All subsequent tasks and exercises within the book are designed to be completed exclusively within Excel, eliminating the need for external programs. However, you will configure a few Excel add-ins as part of the process. Technological Requirements This book is designed for intermediate Excel users eager to discover modern features with which they might not be familiar. To fully benefit from it, you should already be acquainted with the following Excel topics: • Working with absolute-, relative-, and mixed-cell references • Building conditional logic and conditional aggregation functions (IF() state‐ ments, SUMIF()/SUMIFS(), and so forth) • Combining data sources (VLOOKUP(), INDEX()/MATCH(), or other lookup functions) • Sorting, filtering, and aggregating data with PivotTables • Basic plotting (bar charts, line charts, and so forth) If you would like more practice with these topics before continuing, I recommend Microsoft Excel 365 Bible by Michael Alexander and Dick Kusleika (Wiley, 2022). In Part III of the book, you will explore advanced concepts in statistics, program‐ ming, and related areas. Don’t be discouraged if these topics appear challenging at first. There are ample resources to assist you in gaining proficiency, and I will provide helpful references when necessary. The primary objective of this book is to demon‐ strate the vast possibilities that Excel offers. If you prefer to enhance your knowledge first before delving into these topics, I rec‐ ommend reading my book Advancing into Analytics: From Excel to Python and R (O’Reilly, 2021). It offers comprehensive insights and guidance on advanced analytics techniques, Python programming, and various other topics relevant to modern data analytics in Excel. How I Got Here My journey to the data world started with Excel during the early 2010s, before data science and AI had fully taken the world by storm. At that time, Excel often felt like a closed system. If you desired to perform advanced analytics, it was commonly advised to switch to Python or R. For self-service relational data models, Access was recommended. Many of the complex analyses and automations I aimed to accom‐ plish involved cumbersome VBA modules and unwieldy array formulas, making the user experience less than ideal. x | Preface

📄 Page 13

For a while, it appeared that Excel might eventually succumb to obsolescence. How‐ ever, today’s Excel, bolstered by various features and applications, has undergone a remarkable transformation. What Is “Modern Analytics”? Why Excel? Modern analytics refers to the use of advanced tools and techniques to prepare and analyze data, ranging from simple retrospective analyses to predictive modeling and artificial intelligence. In the evolving landscape of data-driven decision making, it’s essential to have tools that are versatile and interoperable, enabling users to perform a wide range of analytics activities. Previously, Excel fell short in meeting these requirements. However, Excel has under‐ gone significant transformation over the past decade, making it a true powerhouse for modern data analytics. This book aims to dispel common misconceptions held by technical professionals about Excel and to demonstrate its capabilities in the modern analytics realm. By showcasing features such as Power Pivot, Power Query, and other tools, this book challenges the belief that Excel is limited to basic formulas and functions. It emphasi‐ zes that today’s Excel has evolved into a robust platform capable of handling complex data analytics tasks. Ultimately, this book showcases Excel as a powerful and versatile tool for modern analytics. It seeks to debunk myths, guiding technical professionals and managers to fully exploit Excel’s potential for effective data analysis and decision making. In doing so, it enables users to harness Excel as a crucial component of the contemporary ana‐ lytics toolkit, providing insights and driving success in our data-driven world. Modern Excel and Interoperability Modern analytics emphasizes interoperability, so it’s not surprising that many tools showcased in this book are also prevalent elsewhere in the analyst’s toolkit. Notably, Power Query and Power Pivot, discussed in Part I and Part II respectively, are also available in Power BI, Microsoft’s business intelligence and reporting tool. Python can also be utilized in Power BI. These tools can be combined in various ways, and as you master one, you’re likely to encounter it in a different context. This book primarily focuses on Excel, but it’s helpful to understand how these elements fit into the broader modern analytics toolkit. Preface | xi

📄 Page 14

Book Overview To meet the learning objective and scope of this book, I’ve divided the content into three parts. Part I, Data Cleaning and Transformation with Power Query Part I focuses on Power Query for data cleaning in Excel, and how it can be used as an extract, transform, load (ETL) tool. You’ll get a tour of the Power Query Editor, learning about data profiling and various transformation techniques such as filtering, splitting, aggregating, and merging data. Part II, Data Modeling and Analysis with Power Pivot Part II introduces Power Pivot for Excel, focusing on its use for reporting. You will learn how to define relationships, build a Data Model, and enhance it with calculated columns, key performance indicators (KPIs), and more—primarily using the Data Analysis Expressions (DAX) language. Part III, The Excel Data Analytics Toolkit Part III of the book explores several exciting new features for data analysis in Excel. You will learn about dynamic array functions, which enable quick and flexible spreadsheet calculations. Additionally, the book provides a primer on predictive ana‐ lytics and AI, discussing their potential applications in Excel and offering a glimpse into the program’s future. The book concludes with an advanced topic: building an automated workbook using Python. You will learn how to effectively leverage Python and Excel together to enhance your analytical capabilities. End-of-Chapter Exercises When I read books, I tend to skip over the exercises at the end of the chapter because I feel keeping the momentum of my reading is more valuable. Don’t be like me! At the end of most chapters, I offer opportunities to apply what you’ve learned through practice. Exercises and their solutions are located in the exercises folder within the accompanying repository, organized into subfolders by chapter number. I encourage you to complete these drills and then compare your responses with the provided solutions. By doing so, you will not only enhance your understanding of the material, but also set a positive example for me. xii | Preface

📄 Page 15

This Is Not a Laundry List Excel’s rapid development pace and the abundance of new tools can be overwhelm‐ ing. To avoid losing focus and making the book unwieldy, I have carefully selected a specific set of topics with broad potential and usefulness for intermediate Excel users, drawing from my years of experience as an Excel consultant and trainer. If your favorite or most impactful feature for modern analytics in Excel is not covered in this book, I encourage you to share your perspective as a valued member of the community. The realm of data analytics in Excel goes beyond the boundaries of a sin‐ gle book, and the Excel community is eager to learn from your insights and experiences. Are you ready to embark on a tour of modern Excel? I’ll meet you in Chapter 1. Conventions Used in This Book The following typographical conventions are used in this book: Italic Indicates new terms, URLs, email addresses, filenames, and file extensions. Constant width Used for program listings, as well as within paragraphs to refer to program ele‐ ments such as variable or function names, databases, data types, environment variables, statements, and keywords. Constant width bold Shows commands or other text that should be typed literally by the user. Constant width italic Shows text that should be replaced with user-supplied values or by values deter‐ mined by context. This element signifies a tip or suggestion. This element signifies a general note. Preface | xiii

📄 Page 16

This element indicates a warning or caution. Using Code Examples Supplemental material (code examples, exercises, etc.) is available for download at https://github.com/stringfestdata/modern-analytics-excel-book. This book is here to help you get your job done. In general, if example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission. We appreciate, but generally do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Modern Data Analytics in Excel by George Mount (O’Reilly). Copyright 2024 Candid World Consulting, LLC, 978-1-098-14882-9.” If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com. O’Reilly Online Learning For more than 40 years, O’Reilly Media has provided technol‐ ogy and business training, knowledge, and insight to help companies succeed. Our unique network of experts and innovators share their knowledge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in-depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit https://oreilly.com. xiv | Preface

📄 Page 17

How to Contact Us Please address comments and questions concerning this book to the publisher: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 800-889-8969 (in the United States or Canada) 707-827-7019 (international or local) 707-829-0104 (fax) support@oreilly.com https://www.oreilly.com/about/contact.html We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at https://oreil.ly/modern-data-analytics-excel. For news and information about our books and courses, visit https://oreilly.com. Find us on LinkedIn: https://linkedin.com/company/oreilly-media. Watch us on YouTube: https://youtube.com/oreillymedia. Acknowledgments One of the most fascinating aspects of writing a book, especially the acknowledg‐ ments, is that it captures a moment in your life and highlights the people who are sig‐ nificant at that time. Many of these names can be found in the acknowledgments to my previous book. I am especially grateful to the acquisitions team at O’Reilly, Michelle Smith and Jon Hassell, for giving me the green light to write another book. My friend and fellow O’Reilly author, Tobias Zwingmann, whose work I have mutually reviewed over the years, provided an exceptionally helpful technical review for this project. Addition‐ ally, my parents, Jonathan and Angela Mount, have been unwavering in their support, more than I could ever ask for. It’s uncertain how many mothers wish their children to become Excel authors, but mine has been incredibly supportive. I also had the opportunity to deepen my acquaintance with some individuals through this project. I extend my thanks to Alan Murray, Joseph Stec, and Meghan Finley for their invaluable additional technical reviews. Meghan, in particular, has not only brought her impressive technical editing experience to the book but has also been an incredible support as my girlfriend throughout the writing process. (As any author will tell you, writing a book inevitably becomes a family affair.) Additionally, I am grateful to Jeff Stevens, Laura Szepesi, and Mark Depow for their feedback on the manuscript. Preface | xv

📄 Page 18

Moreover, I owe a debt of gratitude to the editorial team at O’Reilly, who guided me through the extensive process of writing a technical book. A special thanks to Sara Hunter for being an invaluable editorial sounding board as I embarked on writing my second book. Lastly, I would like to express my appreciation to the entire Excel community for being such a welcoming and inspiring group. This spreadsheet program has opened up more opportunities and introduced me to more incredible people than I could have ever imagined. I hope this book contributes in some small way to your own remarkable journey with Excel. xvi | Preface

📄 Page 19

PART I Data Cleaning and Transformation with Power Query

📄 Page 20

(This page has no text content)