Learning Microsoft Power BI Transforming Data into Insights Jeremey Arnold 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. For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly. 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. 978-1-098-11284-4 [LSI] Dedication I dedicate this book to my wife, Katherine, and my children, Elainamia and Makayla.
They are my constant source of encouragement and joy. 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 bringing 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 asymmetric comprehension problem.
What we historically haven’t had is the ability to process 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 organization 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. 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 comprehend 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 statements. 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 Microsoft Excel? How do we target specific groups or slices of a group for analysis effectively? 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. 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 Modeling 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 elements such as variable or function names, databases, data types, environment variables, statements, and keywords. 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. 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: “Learning Microsoft Power BI by Jeremey Arnold (O’Reilly).” 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. 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. 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. Find us on LinkedIn: https://linkedin.com/company/oreilly-media. Follow us on Twitter: https://twitter.
Watch us on YouTube: https://youtube. 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.