Learning Microsoft Power BI Transforming Data into Insights (Jeremey Arnold) (Z-Library)

A rnold Lea rning M icrosoft Pow er B I Lea rning M icrosoft Pow er B I Jeremey Arnold Learning Microsoft Power BI Transforming Data into Insights

DATA SCIENCE Learning Microsoft Power BI US $59.99 CAN $74.99 ISBN: 978-1-098-11284-4 Twitter: @oreillymedia linkedin.com/company/oreilly-media youtube.com/oreillymedia Microsoft Power BI is a data analytics and visualization tool powerful enough for the most demanding data scientists, but accessible enough for everyday use for anyone who needs to get more from data. The market has many books designed to train and equip professional data analysts to use Power BI, but few of them make this tool accessible to anyone who wants to get up to speed on their own. This streamlined intro to Power BI covers all the foundational aspects and features you need to go from “zero to hero” with data and visualizations. Whether you work with large, complex datasets or work in Microsoft Excel, author Jeremey Arnold shows you how to teach yourself Power BI and use it confidently as a regular data analysis and reporting tool. You’ll learn how to: • Import, manipulate, visualize, and investigate data in Power BI • Approach solutions for both self-service and enterprise BI • Use Power BI in your organization’s business intelligence strategy • Produce effective reports and dashboards • Create environments for sharing reports and managing data access with your team • Determine the right solution for using Power BI offerings based on size, security, and computational needs Jeremey Arnold is senior analytics architect at Onebridge, a large data analytics consulting firm in Indianapolis, Indiana. Jeremey has worked in data analytics for over a decade and has been a Microsoft Power BI user since its release in 2013. His experience covers multiple industries including healthcare, finance, manufacturing, and the public sector, all with a focus on transforming data into insights and enabling truly data-driven environments. A rnold

Jeremey Arnold Learning Microsoft Power BI Transforming Data into Insights Boston Farnham Sebastopol TokyoBeijing

978-1-098-11284-4 [LSI] Learning Microsoft Power BI by Jeremey Arnold Copyright © 2022 Onebridge. 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: Jeff Bleiel Production Editor: Christopher Faucher Copyeditor: nSight, Inc. Proofreader: Sharon Wilkey Indexer: Ellen Troutman-Zaig Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Kate Dullea September 2022: First Edition Revision History for the First Edition 2022-09-19: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781098112844 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Learning Microsoft Power BI, 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.

I dedicate this book to my wife, Katherine, and my children, Elainamia and Makayla. They are my constant source of encouragement and joy.

Table of Contents Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi 1. Intro to Power BI. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 What Is Power BI? 2 Power BI Components 2 Power BI Desktop 4 The Power BI Service 4 The Power Platform 5 How Did We Get to Power BI? 6 SQL Server: Microsoft’s Relational Database 7 SQL Server Analysis Services Multidimensional: One Small Step into BI 7 SQL Server Reporting Services: Pixel-Perfect Reporting, Automated Reports, and More 7 Excel: A Self-Service BI Tool 7 Power Pivot 8 Important New Functionality That Leads to Power BI 8 Power BI Desktop Is Born 8 Power BI Desktop Under the Hood 9 VertiPaq: The Storage Engine 9 DAX: The Formula Engine 10 What Makes Power BI Different from Its Competitors? 10 Conclusion 12 2. The Report and Data Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Report View: Home Section of the Ribbon 14 The Clipboard Subsection 15 v

The Data Subsection 15 The Queries Subsection 21 The Insert Subsection 22 The Calculations Subsection 24 The Final Subsections: Sensitivity and Share 24 Report View: The Insert Tab 25 The Pages Subsection 25 The Visuals Subsection 25 The AI Visuals Subsection 25 The Power Platform Subsection 26 The Elements Subsection 26 Report View: The Modeling Tab 28 The Relationships Subsection 28 The Calculations Subsection 29 The Page Refresh Subsection 29 The What If Subsection 29 The Security Subsection 29 The Q&A Subsection 30 Report View: The View Tab 31 The Themes Subsection 31 The Scale to Fit Subsection 31 The Page Options Subsection 31 The Show Panes Subsection 32 Report View: Help Section 32 Report View: External Tools Section 33 The Pane Interface of the Report View 33 Visualizations Pane 34 Fields and Filters Panes 35 A Quick Rundown of the Other Panes 37 Data View 38 Conclusion 41 3. Importing and Modeling Our Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Getting Our Data 43 The Power Query Ribbon 47 The Home Tab 47 The Transform Tab 52 The Add Column Tab 55 The Model View 57 What Is a Relationship? 58 vi | Table of Contents

The Properties Pane 65 Conclusion 67 4. Let’s Make Some Pictures (Visualizing Data 101). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Why Visualize Data? 69 The Visualizations Pane 71 Fields 73 Format 73 Analytics 73 Visual Interactivity 74 Column and Bar Charts 76 Stacked Bar and Column Charts 77 Clustered Bar and Column Charts 79 100% Stacked Bar and Column Charts 80 Small Multiples 82 Waterfall Chart 82 Line and Area Charts 83 Line Chart 83 Area Chart 84 Stacked Area Chart 85 Line and Stacked Column Chart/Clustered Column Chart 86 Ribbon Chart 87 Donuts, Dots, and Maps, Oh My! 87 Funnel Chart 88 Scatter Chart 89 Pie and Donut Chart 90 Treemap 91 Map Visuals 92 The “Flat” Visuals 93 Gauge 93 Card/Multi-Row Card 94 KPI 95 Table/Matrix 96 Slicer 98 Conclusion 99 5. Aggregations, Measures, and DAX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 A Primer on the DAX Language 101 Measures 102 Calculated Columns 102 Table of Contents | vii

Calculated Tables 103 Types of Functions 103 Aggregations, More Than Some Sums 104 Sum 104 Average 106 Minimum and Maximum 108 Standard Deviation, Variance, and Median 110 Count and Count (Distinct) 111 First, Last, Earliest, and Latest 113 Measures and DAX Fundamentals 114 Implicit and Explicit Measures 114 DAX Syntax Fundamentals 116 CALCULATE 117 We Heard You Like DAX, So We Put Some DAX in Your DAX 120 Row and Filter Context 122 One Final DAX Example 124 Conclusion 126 6. Putting the Puzzle Pieces Together: From Raw Data to Report. . . . . . . . . . . . . . . . . . . 127 Your First Data Import 127 Choose and Transform the Data When You Import 128 Transformations in Power Query 129 Second Data Import and Wrangling 132 Consolidating Tables with Append 134 Using Merge to Get Columns from Other Tables 138 Building Relationships 144 Hiding Tables 145 Identifying Our Relationship Columns 146 Time to Get Building 147 Let’s Get Reporting! 149 We Need a Name... 150 Cards Help Identify Important Data Points 150 Bars, Columns, and Lines 156 Conclusion 160 7. Advanced Reporting Topics in Power BI. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 AI-Powered Visuals 161 Key Influencers 162 Decomposition Tree 165 Q&A 167 viii | Table of Contents

Smart Narrative 172 What-If Analysis 173 Parameter Setup 173 DAX Integration of the Parameter 174 Parameter Modification 176 R and Python Integration 177 Limitations of Using R and Python 177 Enabling R and Python for Power BI 178 R and Python in Power Query 178 R and Python Visuals 179 Conclusion 180 8. Introduction to the Power BI Service. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 The Basics of the Service: What You Need to Know 181 The Navigation Menu 182 Home and Browse 184 Create 185 Data Hub 187 Settings 190 Metrics 193 Apps 193 Deployment Pipelines 195 Learn 195 Publishing Your Work 195 What Is a Workspace? 197 My Workspace 198 Shared Capacity Workspaces 198 Dataflows in Shared Workspaces 201 Putting Your Data in Front of Others 202 Adding Users to a Workspace 202 Sharing via a Link or Teams 203 Sharing via SharePoint 205 Creating an App 205 Conclusion 207 9. Licensing and Deployment Tips. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 Licensing 209 Pro Licensing 210 Premium Per User Licensing 211 Premium Per Capacity, the Big Boy 212 Table of Contents | ix

Workspace and App Management 215 Workspace Generation and Access Control 216 Managing Users in a Workspace 220 Adding Users to Roles for RLS Implementation 222 App Creation and Management 223 The Golden Dataset(s) 228 Conclusion 229 10. Third-Party Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231 Get to Know Business Ops 232 Add External Tools, Remove External Tools, and Modify Display Order 233 Learning, Theme Generation, Visual Generation 235 Additional DAX Resources 239 DAX Studio 241 Tabular Editor 245 Creating Roles 248 Table and Measure Management 249 The ALM Toolkit for Power BI 251 Bravo 254 Analyze Model 255 DAX Formatting 256 Manage Dates 256 Export Data 257 Conclusion 258 A. Commonly Used DAX Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 B. Some Favorite Custom Visuals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 x | Table of Contents

Preface I was a senior at Ball State University when one of my professors, Dr. James McClure, and I were discussing classic challenges to the perfectly competitive market model. We were having a back-and-forth, and throughout our entire discussion, I kept bring‐ ing all the critiques back to a single problem: asymmetric information. Asymmetric information is the condition in which one party knows more about the topic at hand than the other party, to the point where they can use that for some sort of advantage. We live in a world in which Google, Facebook, Amazon, Netflix, and others know so much about you that they can try to figure out what you want before you know you even want it. However, you have access to that same information. In the information age, we don’t have an asymmetric information problem as much as we have an asym‐ metric comprehension problem. What we historically haven’t had is the ability to pro‐ cess data in the same way. Or at least, we didn’t until recently. Tools that would allow you to aggregate information at scale were historically tools of organizations that could afford complicated investments into data platforms that the ordinary person could neither comprehend nor afford. However, today there exists a piece of software that puts one of, if not the most powerful analytics engines ever made into your hands with an initial investment cost of zero dollars and zero cents. We have never been more awash in data, and that data is more available to people like you and me than it ever has been in human history. Microsoft’s Power BI platform gives users a tool to aggregate incredibly large amounts of data to discover insights that can give you just as much, if not more, information than those around you. Whether you are using it for personal reasons or as an orga‐ nization looking to get a competitive edge in the marketplace by making data more meaningful within your company, there has never been a lower-cost entry to data processing with the ease of use of Power BI Desktop. xi

Microsoft has spent years working with companies all over the world on a technology for complicated data analytics. Power BI is built on that technology, and Microsoft is literally putting all that know-how into your hands. Data is the great equalizer. It’s not just about having more or less of it. It’s about using the data you do have effectively. Organizations all over the world collect more data than you and I could ever compre‐ hend in a lifetime, and yet they do nothing with it because they have no idea how to use it, and they find themselves losing market share to smaller competitors who are using the data they have effectively. Nonprofits are using Power BI to do data analysis that makes the world a better place to live, on issues from conservation to climate change to healthcare access. Citizen data analysts are using publicly available datasets to uncover financial misbehavior and to double-check results from data provided by organizations and governments around the world. If the ability to process and make data meaningful is truly the great equalizer of the information age, then Power BI is a tool that gives you the ability to sit at a table and look giants in the eye. You might be an accountant looking to automate complicated data cleaning processes for regulatory purposes and want a tool to quickly visualize profit and loss state‐ ments. You might be a citizen data analyst looking for a tool to help crunch millions of records of data for a personal project. You could be a data scientist looking for a tool to accelerate adoption of your work by end users. If you are a person who works with data in any capacity and want to get more out of that data than you ever have, then Power BI is an ecosystem that you should have exposure to. I wrote this book because, first, I’m super passionate about data being used effectively and I truly believe that everyone in the 21st century can interact with data in some way to improve, either professionally or personally. Second, Power BI has been a vehicle for me to better understand all sorts of important data concepts, and I think those ideas are important to accomplishing that first goal. How do we put data from different sources together? How do we deal with tables that are too large for Micro‐ soft Excel? How do we target specific groups or slices of a group for analysis effec‐ tively? How do we visualize those results to make them comprehensible to our audience? My early career was spent deep in the bowels of corporate finance, and if I had Power BI then, I would have saved so much time and heartache in manually manipulating data and doing simple groupings and pivot tables. Our 21st-century data requires a 21st-century tool to unlock it. I believe Power BI is the best tool to do that. It can store the data. It can analyze the data. It has the reach to be available to anyone who uses Windows. No other data visualization or exploration tool can make that claim, and that’s why I’m excited you’re picking up this book. And I hope you find your data journey as fulfilling as mine has been and continues to be. xii | Preface

Navigating This Book This book is organized roughly as follows: • Chapter 1, “Intro to Power BI”, provides a brief history of Microsoft’s previous business intelligence efforts and how those products have evolved into the Power BI we know today. Alongside that, it goes into detail about how Power BI works under the hood, in terms of how it stores and queries that data. • Chapter 2, “The Report and Data Views”, and Chapter 3, “Importing and Model‐ ing Our Data”, introduce portions of the Power BI user interface, including how to navigate the various ribbons and how to bring your data into Power BI for analysis. • Chapter 4, “Let’s Make Some Pictures (Visualizing Data 101)”, and Chapter 5, “Aggregations, Measures, and DAX”, go into basic principles of visualization and utilizing data aggregations. • Chapter 6, “Putting the Puzzle Pieces Together: From Raw Data to Report”, is a walk-through using the work of the previous chapters to go from nothing in Power BI to a fully functional report page. • Chapter 7, “Advanced Reporting Topics in Power BI”, discusses some advanced analytics topics in Power BI, including AI visuals and what-if analysis. • Chapter 8, “Introduction to the Power BI Service”, and Chapter 9, “Licensing and Deployment Tips”, introduce the Power BI service, the cloud-based platform for sharing reports and insights. • Chapter 10, “Third-Party Tools”, introduces useful third-party tools to accelerate or ease future development. • Appendix A, “Commonly Used DAX Expressions”, and Appendix B, “Some Favorite Custom Visuals”, provide examples of DAX functions for you to take and modify for your own future data, and a valuable list of some of my favorite custom visuals and their various functionalities, respectively. 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. Preface | xiii

Using Code Examples Supplemental material (code examples, exercises, etc.) is available for download at https://oreil.ly/MS-power-BI-files. If you have a technical question or a problem using the code examples, please send email to bookquestions@oreilly.com. 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 per‐ mission. We appreciate, but generally do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Learning Microsoft Power BI by Jeremey Arnold (O’Reilly). Copyright 2022 Onebridge, 978-1-098-11284-4.” 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

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-998-9938 (in the United States or Canada) 707-829-0515 (international or local) 707-829-0104 (fax) 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/microsoft-power-BI. Email bookquestions@oreilly.com to comment or ask technical questions about this book. For news and information about our books and courses, visit https://oreilly.com. Find us on LinkedIn: https://linkedin.com/company/oreilly-media. Follow us on Twitter: https://twitter.com/oreillymedia. Watch us on YouTube: https://youtube.com/oreillymedia. Acknowledgments I want to thank everyone at Onebridge who believed in me and gave me the space to write this text. I want to give a special thanks to Sheryl Ricci for helping me with internal editing. Thanks to the book’s technical reviewers—Bradley Nielsen, Belinda Allen, and Bill McLellan. I want to thank the team at O’Reilly who gave me this opportunity, especially Michelle Smith, Jeff Bleiel, and Chris Faucher. Finally, I want to thank you, the reader, for taking the time to pick up this text and give it a chance. Preface | xv

CHAPTER 1 Intro to Power BI You’re a data person. You understand your data. You know how spreadsheets work. But there’s so much data to process, and your spreadsheets aren’t cutting it. You need a way to visualize the data and share it with business users so that they can see the analytics, understand the data as you do, and even manipulate the visualizations with little to no training. If that’s why you’re looking at this book, you made the right move. Microsoft Power BI is exactly what you need. This book will show you how to get up to speed quickly—so quickly that you’ll be building and publishing reports that will wow your colleagues and make your mom proud. Microsoft Power BI is a data analytics and visualization tool powerful enough for the most demanding data scientists but accessible enough for everyday use by anyone needing to get more from their data. In the beginning, back when life was simpler (in 2011), Power BI was just a simple piece of desktop software. But it isn’t anymore. It’s an entire business intelligence eco‐ system that can fit into multiple diverse technology stacks. This chapter introduces Microsoft Power BI, discusses the entire Power BI family of products, provides an overview of how Power BI works, and looks at what distin‐ guishes it from other similar tools. By the end of this chapter, you’ll: • Know what components fit in the Power BI ecosystem and why they’re important. • Learn the history of Microsoft’s business intelligence work to learn how that got us to Power BI. • Discover what makes Power BI different from its competitors. 1

What Is Power BI? Power BI is both a piece of software and a larger ecosystem of products. Usually when people throw out the term “Power BI,” it’s in reference to the desktop authorship soft‐ ware. However, when discussing how most people will (visually) share the fruits of their work with others, it’s done in the context of the Power BI service, a software-as- a-service (SaaS) solution that hosts Power BI datasets and reports that can be used by others who have access. Even beyond these two features, a wide variety of products in the family allow you to embed reports into websites and other applications, view reports on your mobile device, and even have your own version of the SaaS solution on premises. This book focuses on Power BI Desktop and the Power BI service since they are your most basic and valuable building blocks. Power BI Desktop is a tool for data investigation and visualization. Analysts can take data and create interactive reports that enable end users to garner insights that were previously buried. In finance, you might use Power BI to automate the generation of profit and loss (P&L) statements or analyze costs over time. In construction, you could use Power BI to identify variances in times to complete projects based on team composition or geographical factors. In retail, you might identify which of your prod‐ ucts are the most successful, while pinpointing which ones might be on the cusp of taking off if given a bit more of a push via a what-if analysis. According to Microsoft at the 2021 Business Applications Summit, 97% of the For‐ tune 500 uses Power BI in some capacity. That means it’s a technology you can trust putting your time and effort into, especially if you’re looking for the kind of insight that transforms your enterprise. Or in my case, it’s the excuse to build a Pokédex for my daughter. Sometimes you just really want to be the best, like no one ever was. Power BI Components Power BI today consists of a wide variety of products that allow users to create and consume reports from your data. According to Microsoft (at the time of publishing), here are all the components that make up the Power BI family of products: • Power BI Desktop • Power BI service • Power BI Mobile • Power BI Report Builder • Power BI Report Server on premises • Power BI Embedded There is much to unpack in these products, but the main focus of this book is on the first two components. We’ll spend most of our time learning Power BI Desktop because that’s the foundation you need; it’s what the whole ecosystem is built around. Then we’ll discuss the Power BI service in more detail toward the end because you’re 2 | Chapter 1: Intro to Power BI