Introducing Microsoft Power BI

1 IntroducingMicrosoft Power BIAlberto Ferrari and Marco RussoPUBLISHED BY Microsoft Press A division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright 2016 by Microsoft Corporation All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher. ISBN: 978-1-5093-0228-4 Microsoft Press books are available through booksellers and distributors worldwide. If you need support related to this book, email Microsoft Press Support at Please tell us what you think of this book at This book is provided as-is and expresses the author s views and opinions. The views, opinions and information expressed in this book, including URL and other Internet website references, may change without notice.

2 Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred. Microsoft and the trademarks listed at on the Trademarks webpage are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. Acquisitions and Developmental Editor: Rosemary Caperton Editorial Production: Dianne Russell, Octal Publishing, Inc. Copyeditor: Bob Russell, Octal Publishing, Inc. Technical Reviewer: Ed Price; Technical Review services provided by Content Master, a member of CM Group, Ltd. Cover: Twist Creative Seattle Hundreds of titles available Books, eBooks, and online resources from industry experts Free shipping eBooks in multiple formats Read on your computer, tablet, mobile device, or e-reader Print & eBook Best Value Packs eBook Deal of the Week Save up to 60% on featured titles Newsletter and special offers Be the first to hear about new releases, specials, and more Register your book Get additional benefits Visit us today atiii Foreword Contents Introduction.

3 Viii Downloads .. xi Installing the companion content .. xii Acknowledgments .. xii Free ebooks from Microsoft Press .. xiv Errata, updates, & book support .. xiv We want to hear from you .. xv Stay in touch .. xv Chapter 1: Introducing Power BI .. 1 Getting started with Power BI .. 4 Uploading data to Power BI .. 10 Introducing natural-language queries .. 13 Introducing Quick Insights .. 16 Introduction to reports .. 22 Introducing Visual Interactions .. 30 Decorating the report .. 37 Saving the report .. 40 Pinning a report .. 41 iv Contents Refreshing the budget workbook .. 43 Filtering a report .. 50 Conclusions .. 55 Chapter 2: Sharing the dashboard .. 57 Inviting a user to see a dashboard .. 58 Inviting users outside your organization .. 66 Creating a group workspace in Power BI.

4 71 Turning on sharing with Microsoft OneDrive for Business .. 76 Viewing reports and dashboards on mobile 94 Conclusions .. 101 Chapter 3: Understanding data refresh .. 103 Introducing data refresh .. 105 Introducing the Power BI refresh architecture .. 107 Introducing Power BI 111 Publishing to Power BI .. 117 Installing the Power BI Personal Gateway .. 120 Configuring automatic refresh .. 128 Conclusions .. 130 Chapter 4: Using Power BI Desktop .. 132 v Contents Connecting to a database .. 134 Loading from multiple sources .. 141 Using Query Editor .. 145 Hiding or removing tables .. 159 Handling seasonality and sorting months .. 163 Conclusions .. 179 Chapter 5: Getting data from services and content packs .. 181 Consuming a service content pack.

5 183 Creating a custom dataset from a service .. 197 Creating a content pack for your organization .. 211 Consuming an organizational content pack .. 216 Updating an organizational content pack .. 223 Conclusions .. 227 Chapter 6: Building a data model .. 230 Loading individual tables .. 232 Implementing measures .. 236 Creating calculated columns .. 239 Improving the report by using measures .. 242 Integrating budget information .. 244 vi Contents Reallocating the budget .. 256 Conclusions .. 262 Chapter 7: Improving Power BI reports .. 264 Choosing the right visualizations .. 267 Choosing between standard visuals .. 274 Using custom visualizations .. 283 First steps with custom visualizations .. 284 Improving reports by using custom visualizations.

6 291 Identifying conditions when custom visualizations are required .. 299 Using DAX in data models .. 303 Creating high-density reports .. 311 Conclusions .. 320 Chapter 8: Using Microsoft Power BI in your company .. 323 Getting data from existing systems .. 325 Understanding differences between data refresh and live connections .. 328 Using relational databases on-premises .. 330 Using relational databases in the cloud .. 335 Using live connections to Analysis Services .. 338 vii Contents Integrating Power BI with Office .. 340 Publish Excel data models in Power BI .. 340 Consume Power BI content from Excel .. 343 Using Power BI Tiles from Office Store .. 350 Managing security to access data .. 360 Using row-level security .. 364 Extending and customizing Power BI.

7 370 Creating custom visualizations for Power BI .. 371 Introducing the Power BI REST API .. 372 Pushing real-time data to Power BI dashboards .. 376 Power BI embedded in applications .. 381 Conclusions .. 383 About the authors .. 386 Available this fall! Be the first to receive an exclusive pre-order discount for Analyzing Data with Microsoft Power BI and Power Pivot for Excel by expert trainers Alberto Ferrari and Marco Russo. It s easy: Sign up to receive special offers from Microsoft Press Watch your inbox for an exclusive email offer during the first week of SeptemberVisit to get started. Even better? You ll receive a code* to save 35% on your next purchase at the Microsoft Press more about Power BI at code valid on single book or eBook purchase from Code cannot be combined with eBook Deal of the Day, Official Microsoft Practice Tests fulfilled by MeasureUp, or any other offer.

8 Offer expires November 31, 2016. Exclusive offer for Introducing Microsoft Power BI readers viii Introduction Introduction Microsoft introduced the idea of Self-Service Business Intelligence (BI) back in 2009, announcing Power Pivot for Microsoft Excel 2010. Strangely, at that time, it did not make big announcements, hold conferences, or undertake a big marketing campaign for it. Everything started slowly, with some enthusiastic users adopting the new technology, but the vast majority of people did not even know about its existence. As part of the community of BI professionals, we were very surprised by that approach. At the time, we could clearly see the advantages for users to begin adopting Power Pivot as a tool for gathering insights from data, so this complete lack of marketing was somewhat disappointing.

9 Thus, for several years we (as a community) kept asking Microsoft what they were waiting for; what was the delay in promoting Self-Service BI to the greater audience of data analysts, data scientists, decision makers, and BI enthusiasts all over the planet. We asked for the ability to share reports with a team, and the answer was to use SharePoint, either on-premises or the online version, with the first release of Power BI an ix Introduction experience that was still not completely satisfactory. While we were waiting for Microsoft to fix the issues with the previous versions and to begin advertising the current products, it was doing something different that, with the benefit of hindsight, looks to have been the perfect choice. Microsoft collected the feedback of users, carefully considered what was missing in the world of end-user BI, and then crafted the version of Power BI that s available to you today.

10 Power BI is an evolution of the add-ins previously available in Excel: Power Pivot, Power Query, and Power View. You can use Power BI with or without Excel you no longer are dependent on the version of Microsoft Office installed at your company. People did not like to share reports by using only SharePoint, and Microsoft moved away from it. Users wanted a mobile experience, and the development team created it. Data analysts wanted Power , simplicity, new visualizations, and all of this is now available in Power BI. In addition, a lot of effort went into the creation of a seamless experience in loading data from many different cloud sources and building the infrastructure needed to provide all BI enthusiasts with a framework with which they can grow their reports, share them with their teams, and refresh the data in a simple yet effective way.