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

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

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. 978-1-098-14882-9 [LSI]

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 analytics tools. Learning Objective By the end of this book, you should be able to use modern Excel tools for data cleaning, analysis, reporting, and advanced analytics. In particular, you’ll clean and transform 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 computer 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 the integration of Python with Excel, guiding you through the process of downloading 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() statements, 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, programming, 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 demonstrate the vast possibilities that Excel offers. If you prefer to enhance your knowledge first before delving into these topics, I recommend 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 accomplish involved cumbersome VBA modules and unwieldy array formulas, making the user experience less than ideal. For a while, it appeared that Excel might eventually succumb to obsolescence. However, 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 undergone 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 emphasizes 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 analytics 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.

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 analytics 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. This Is Not a Laundry List Excel’s rapid development pace and the abundance of new tools can be overwhelming. 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 single 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 elements 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 determined by context. TIP This element signifies a tip or suggestion. NOTE This element signifies a general note. WARNING 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 NOTE For more than 40 years, O’Reilly Media has provided technology 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. 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 acknowledgments, is that it captures a moment in your life and highlights the people who are significant 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. Additionally, 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. 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.

Part I. Data Cleaning and Transformation with Power Query

Chapter 1. Tables: The Portal to Modern Excel Excel boasts an extensive array of analytical tools, which can make it challenging to determine the best starting point. However, a fundamental step is mastering the Excel table. This chapter delves into the essential elements of Excel tables, acting as a conduit to Power Query, Power Pivot, and additional tools highlighted in this book. It further underscores the significance of organizing data within a table meticulously. To engage with this chapter’s content, navigate to ch_01.xlsx in the ch_01 folder located within the companion repository of the book. Creating and Referring to Table Headers A dataset without column headers is practically useless, as it lacks meaningful context for interpreting what each column measures. Unfortunately, it’s not uncommon to encounter datasets that break this cardinal rule. Excel tables act as a valuable reminder that the quality of a dataset hinges on the presence of clear and informative headers. In the start worksheet of ch_01.xlsx, you will come across data in columns A:F without corresponding headers, which are currently located in columns H:M. This design is far less than optimal. To adjust it, click anywhere within the primary data source and proceed from the ribbon to Insert → Table → OK, as illustrated in Figure 1-1. Alternatively, you can press Ctrl+T or Ctrl+L from within the data source to launch the same Create Table dialog box.