Learn Power BI: A comprehensive, step-by-step guide for beginners to learn real-world business intelligence, 2nd Edition

458 Pages·2022·17.05 MB·english

by  Greg Deckler

Learn Power BI
Second Edition
A comprehensive, step-by-step guide for beginners
to learn real-world business intelligence
Greg Deckler
BIRMINGHAM—MUMBAI
Learn Power BI
Second Edition
Copyright © 2021 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted
in any form or by any means, without the prior written permission of the publisher, except in the case of
brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information
presented. However, the information contained in this book is sold without warranty, either express or
implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for
any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and
products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot
guarantee the accuracy of this information.
Publishing Product Manager: Reshma Raman
Senior Editor: David Sugarman
Content Development Editor: Nathanya Dias
Technical Editor: Sonam Pandey
Copy Editor: Safis Editing
Project Coordinator: Aparna Ravikumar Nair
Proofreader: Safis Editing
Indexer: Subalakshmi Govindhan
Production Designer: Ponraj Dhandapani
First published: September 2019
Second edition: December 2021
Production reference: 1121121
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-80181-195-8
www.packt.com
Contributors
About the author
Greg Deckler is a Microsoft MVP for Data Platform and an active member of the
Columbus Ohio IT community, having founded the Columbus Azure ML and Power BI
User Group (CAMLPUG) and presented at many conferences and events throughout the
country. An active blogger and community member interested in helping new users of
Power BI, Greg actively participates in the Power BI community, having authored over
180 Power BI Quick Measures Gallery submissions and over 5,000 authored solutions
to community questions. Greg is Vice President of cloud services at Fusion Alliance, a
regional consulting firm, and assists customers in gaining competitive advantage from the
cloud and cloud first technologies such as Power BI.
Thanks to my son Rocket, mom Sandy, the real-life Pam, Pam Hagely, and
the entire Power BI community for their support, in particular, Pragati
Jain, Pat Mahoney, Miguel Felix, Imke Feldman, Parvinder Chana, Paul
Brown, Marco Russo, Ed Hansberry, Allison Kennedy, Tom Martens,
Gilbert Quevauvilliers, Konstantinos Ioannou, Fowmy Abdulmuttalib,
Matt Allington, Mike Carlo, Seth Bauer, and Phil Seamark. Special
thanks to Doug Brown, Tom Wood, Tom Campbell, Rick Mariotti, David
LeVine, Megan Koontz, Kishan Wanigasingha, Ishara Guruge, Sajith
Wanigasingha, Paul Moore, Rob Watkins, Jon Tokash, John Dages, David
Schroeder, and my entire Fusion Alliance family, including "work friend"
Ashley Titus. This book is dedicated to my dad, Carl, who passed away
in 2021.
About the reviewer
Peter Ter Braake started working as a developer in 1996 after studying physics in
Utrecht, the Netherlands. Databases and business intelligence piqued his interest
the most, leading to him specializing in SQL Server and its business intelligence
components. He has worked with Power BI from the tool's very beginnings. Peter
started working as an independent contractor in 2008. This has enabled him to divide
his time between teaching data-related classes, consulting with customers, and writing
articles and books. Peter has also authored Data Modeling for Azure Data Services, Packt.
Table of Contents
Preface
Section 1: The Basics
1
Understanding Business Intelligence and Power BI
Exploring key concepts of The extended Power BI ecosystem 17
business intelligence 4
Choosing the right Power BI
Domain 5
license 18
Data 6
Shared capacity 18
Model 8
Dedicated capacity 20
Analysis 10
Visualization 12 Introducing Power BI Desktop
and the Power BI service 22
Discovering the Power BI
Power BI Desktop 23
ecosystem 13
The Power BI service 24
Core and Power BI-specific 14
Core and non-Power BI-specific 14 Summary 25
Non-core and Power BI-specific 15 Questions 26
Natively integrated Microsoft
Further reading 26
technologies 16
2
Planning Projects with Power BI
Planning Power BI business Procuring the required resources 32
intelligence projects 28 Discovering the required data sources 35
Identifying stakeholders, goals, and Designing a data model 35
requirements 28 Planning reports and dashboards 38
vi Table of Contents
Explaining the example scenario 38 Designing a data model 41
Background 39 Planning reports and dashboards 42
Identifying stakeholders, goals, and
Summary 42
requirements 39
Questions 43
Procuring the required resources 40
Discovering the required data sources 41 Further reading 43
Section 2: The Desktop
3
Up and Running with Power BI Desktop
Technical requirements 48 Generating data 57
Downloading and running Creating a calculated table 58
Power BI Desktop 48 Creating calculated columns 60
Downloading Power BI Desktop 48 Formatting columns 62
Running Power BI Desktop 51
Creating visualizations 63
Touring the desktop 52 Creating your first visualization 63
Header 52 Formatting your visualization 66
Views 53 Adding analytics to your visualization 69
Panes 53 Creating and using a slicer 70
Canvas 54 Creating more visualizations 73
Wallpaper 54 Editing visual interactions 77
Pages 54
Summary 80
Footer 54
Questions 81
Ribbon 55
Further reading 81
The Formula Bar 56
4
Connecting to and Transforming Data
Technical requirements 84 Touring the Power Query Editor 92
Getting data 84 Transforming budget and forecast data 95
Transforming People, Tasks, and
Creating your first query 84
January data 100
Getting additional data 90
Merging, copying, and
Transforming data 91
appending queries 103
Table of Contents vii
Merging queries 103 Organizing queries 113
Expanding tables 106 Checking column quality, distribution,
Disabling queries from being loaded 107 and profiles 114
Copying queries 107 Loading the data 116
Changing sources 108
Summary 116
Appending queries 109
Questions 117
Verifying and loading data 111
Further reading 117
5
Creating Data Models and Calculations
Technical requirements 120 Checking and troubleshooting
Creating a data model 120 calculations 146
Touring the Model view 120 Boundary cases 146
Modifying the layout 122 Slicing 148
Creating and understanding Grouping 150
relationships 124
Summary 152
Exploring the data model 129
Questions 153
Creating calculations 130
Further reading 153
Calculated columns 130
Measures 137
6
Unlocking Insights
Technical requirements 156 Top-N filtering 186
Segmenting data 156 Gauges and KPIs 187
What-if parameters 188
Creating groups 156
Conditional formatting 190
Creating hierarchies 158
Quick measures 192
Understanding RLS 161
Report tooltip pages 194
Buttons 167
Key influencers 196
Question and answer (Q&A) 171
Bookmarks 177 Summary 197
Advanced analysis techniques 183 Questions 197
The Analyze and Summarize features 183 Further reading 198
viii Table of Contents
7
Creating the Final Report
Technical requirements 200 Creating the Division Management page 219
Preparing the final report 200 Creating the Branch Management page 223
Creating the Hours Detail page 227
Planning the final report 200
Creating the Employee Details page 232
Cleaning up 201
Creating the Introduction page 234
Using a theme 201
Creating a page template 204 Finishing up 235
Using Sync slicers 212
Testing 236
Adjusting the calendar 214
Cleaning up 237
Adding report filters 214
Summary 238
Creating the final report pages 215
Questions 238
Creating the Executive Summary page 215
Further reading 239
Section 3: The Service
8
Publishing and Sharing
Technical requirements 244 Publishing and sharing 250
Getting an account 244 Creating a workspace 250
Office 365 244 Publishing 252
Power BI trial 244 Sharing 256
Introducing the service 245 Summary 259
Touring the service 245 Questions 259
Header 246 Further reading 260
Navigation pane 248
Canvas 250
Table of Contents ix
9
Using Reports in the Power BI Service
Technical requirements 262 Refresh visuals 285
Viewing and using reports 262 Comment 285
Add to Favorites 287
File menu 263
Export menu 273 Editing and creating reports 287
Share 275
Editing reports 288
Chat in Teams 278
Creating a report 290
Subscribe 278
Ellipsis (…) 281 Summary 291
Reset 281 Questions 291
Bookmark 282 Further reading 292
View 284
10
Understanding Dashboards, Apps, Goals, and Security
Technical requirements 294 Understanding security and
Understanding dashboards 294 permissions 317
Creating a dashboard 294 Workspace permissions 318
Working with dashboards 296 App permissions 320
Working with tiles 302 Object permissions 320
RLS 322
Creating and using apps 306
Summary 323
Creating an app 306
Questions 324
Getting and using apps 311
Further reading 325
Working with goals 313
Creating scorecards and goals 313
Using scorecards and goals 315