Power Pivot and Power BI: The Excel User's Guide to DAX Power Query, Power BI & Power Pivot in Excel 2010-2016, 2nd Edition
- 5h 11m
- Avi Singh, Rob Collie
- Tickling Keys
- 2016
Microsoft PowerPivot is a free add-on to Excel from Microsoft that allows users to produce new kinds of reports and analyses that were simply impossible before, and this book is the first to tackle DAX formulas, the core capability of PowerPivot, from the perspective of the Excel audience. Written by the world’s foremost PowerPivot blogger and practitioner, the book’s concepts and approach are introduced in a simple, step-by-step manner tailored to the learning style of Excel users everywhere. The techniques presented allow users to produce, in hours or even minutes, results that formerly would have taken entire teams weeks or months to produce. It includes lessons on the difference between calculated columns and measures; how formulas can be reused across reports of completely different shapes; how to merge disjointed sets of data into unified reports; how to make certain columns in a pivot behave as if the pivot were filtered while other columns do not; and how to create time-intelligent calculations in pivot tables such as “Year over Year” and “Moving Averages” whether they use a standard, fiscal, or a complete custom calendar. The “pattern-like” techniques and best practices contained in this book have been developed and refined over two years of onsite training with Excel users around the world, and the key lessons from those seminars costing thousands of dollars per day are now available to within the pages of this easy-to-follow guide. This updated second edition covers new features introduced with Office 2015.
About the Authors
One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world's first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. In 2010, he realized that Power Pivot was not "just another tech," and that its power resided not in the software itself, but in the unique way it empowers human beings.
Inspired, he left Microsoft and founded a consulting and training company (PowerPivotPro) to make that human empowerment his full-time mission. Hundreds of clients later, he and his colleagues have seen the same theme overwhelmingly repeat itself: this toolset doesn't just increase efficiency. It doesn't just reduce costs. It doesn't just lead to dramatically smarter decisions and same-day agility. It also makes people – and organizations – happier. Above all else, Rob and his colleagues want you to experience that for yourself.
When he's not training, consulting, speaking, or writing, Rob can be found in his laboratory, devising new tools and techniques for the Power Bl community. He is the author of two books, including the world's #1-selling Power Bl book (the precursor to this one), has penned over 600 articles and whitepapers, and also occasionally sleeps.
Avichal "Avi" Singh most recently worked at Microsoft, where he built a successful Power Pivot Bl platform serving 600+ users − replacing the far more expensive and less effective Bl methodology formerly utilized by that division.
Avi has personally lived the amazing transformation that Power Pivot brings to our professional lives. His passion for empowering others led him, like Rob, to depart Microsoft and make that his full-time focus at PowerPivotPro. Since joining the team, Avi has worked closely with clients all over the US, ranging in size from 5 employees all the way up to the Fortune 500.
Avi has a Masters in Computer Science from the University of Washington and spent 10+ years as a business analyst across various verticals before discovering Power Pivot. He brings an infectious energy to everything he does, which keeps the rest of the team on its toes. In between client engagements, he has been co-authoring this book (and becoming increasingly suspicious that Rob tricked him into it).
In this Book
-
Supporting Workbooks and Data Sets
-
Errata and Book Support
-
A Note on Hyperlinks
-
Foreword and Forward
-
Introduction - Our Two Goals for This Book
-
A Revolution Built on YOU
-
Power Pivot and the Power BI Family—Making Sense of the Various Versions
-
Learning Power Pivot “The Excel Way”
-
Loading Data into Power Pivot
-
Intro to Calculated Columns
-
Introduction to DAX Measures
-
The “Golden Rules” of DAX Measures
-
CALCULATE() – Your New Favorite Function
-
ALL() – The “Remove a Filter” Function
-
Thinking in Multiple Tables
-
“Intermission” – Taking Stock of Your New Powers
-
Disconnected Tables
-
Introducing the FILTER() Function, and Disconnected Tables Continued
-
Introduction to Time Intelligence
-
IF(), SWITCH(), BLANK(), and other Conditional Fun
-
SUMX() and other X (“Iterator”) Functions
-
Multiple Data Tables
-
Multiple Data Tables – Differing Granularity
-
Performance—Keep Things Running Fast
-
Power Query to the Rescue
-
Power BI Desktop
-
“Complicated” Relationships
-
Row and Filter Context Demystified
-
CALCULATE and FILTER – More Nuances
-
Time Intelligence with Custom Calendars—Greatest Formula in the World
-
Advanced Calculated Columns
-
New DAX Functions… and Variables!
-
“YouTube for Data” – The Importance of a Server
-
PS—Can We Ask You for a Special Favor?
