Table of Contents
Introduction 1
About This Book 2
Foolish Assumptions 3
Icons Used in This Book 4
Beyond the Book 5
Part 1 Put Your BI Thinking Caps On 7
Chapter 1 A Crash Course in Data Analytics Terms: Power BI Style 9
What Is Data, Really? 10
Working with structured data 10
Looking at unstructured data 11
Adding semistructured data to the mix 11
Looking Under the Power BI Hood 12
Posing questions with Power Query 13
Modeling with Power Pivot 14
Visualizing with Power View 14
Mapping data with Power Map 14
Interpreting data with Power Q&A 14
Power BI Desktop 15
Power BI Services 15
Knowing Your Power BI Terminology 15
Capacities 16
Workspaces 16
Reports 18
Dashboards 19
Navigation pane 20
Business Intelligence (BI): The Definition 21
Chapter 2 The Who, How, and What of Power BI 23
Highlighting the Who of Power BI 24
Business analyst 24
Data analyst 24
Data engineer 25
Data scientist 26
Database administrator 26
Understanding How Data Comes to Life 27
Prepare 27
Model 28
Visualize 29
Analyze 30
Manage 30
Examining the Various Types of Data Analytics 31
Taking a Look at the Big Picture 32
Chapter 3 Oh, the Choices: Power BI Versions 33
Why Power BI versus Excel? 33
Power BI Products in a Nutshell 35
Introducing the Power BI license options 35
Looking at Desktop versus Services options 36
Stacking Power BI Desktop against Power BI Free 38
Examining the Details of the Licensing Options 38
Seeing how content and collaboration drive licensing 39
Starting with Power BI Desktop 40
Adding a Power BI Free license 41
Upgrading to a Power BI Pro license 42
Going all in with a Power BI Premium license 43
On the Road with Power BI Mobile 44
Working with Power BI Report Server 45
Linking Power BI and Azure 46
Chapter 4 Power BI: The Highlights 47
Power BI Desktop: A Top-Down View 47
Ingesting Data 49
Files or databases? 49
Building data models 52
Analyzing data 53
Creating and publishing items 54
Services: Far and Wide 55
Viewing and editing reports 56
Working with dashboards 60
Collaborating inside Power BI Services 61
Refreshing data 62
Part 2 It's Time to have a Data Party 65
Chapter 5 Preparing Data Sources 67
Getting Data from the Source 67
Managing Data Source Settings 72
Working with Shared versus Local Datasets 73
Storage Modes 76
Dual mode 77
Considering the Query 77
Addressing and correcting performance 79
Diagnosing queries 80
Exporting Power BI Desktop Files and Leveraging XMLA 81
Chapter 6 Getting Data from Dynamic Sources 85
Getting Data from Microsoft-Based File Systems 86
Working with Relational Data Sources 87
Importing data from a relational data source 89
The good ol' SQL query 91
Importing Data from a Nonrelational Data Source 92
Importing JSON File Data into Power BI 93
Importing Data from Online Sources 95
Creating Data Source Combos 97
Connecting and importing data from Azure Analysis Services 98
Accessing data with Connect Live 99
Dealing with Modes for Dynamic Data 99
Fixing Data Import Errors 100
"Time-out expired" 100
"The data format is not valid" 101
"Uh-oh - missing data files" 101
"Transformation isn't always perfect" 102
Chapter 7 Cleansing, Transforming, and Loading Your Data 103
Engaging Your Detective Skills to Hunt Down Anomalies and Inconsistencies 104
Checking those data structures and column properties 105
Finding a little help from data statistics 106
Stepping through the Data Lifecycle 107
Resolving inconsistencies 108
Evaluating and Transforming Column Data Types 111
Finding and creating appropriate keys for joins 111
Shaping your column data to meet Power Query requirements 113
Combining queries 115
Tweaking Power Query's M Code 121
Configuring Queries for Data Loading 123
Resolving Errors During Data Import 125
Part 3 The Art and Science of Power BI 127
Chapter 8 Crafting the Data Model 129
An Introduction to Data Models 129
Working with data schemas 130
Storing values with measures 134
Working with dimensions and fact tables (yet again) 136
Flattening hierarchies 137
Dealing with Table and Column Properties 139
Managing Cardinality and Direction 141
Cardinality 142
Cross-filter direction 142
Data Granularity 144
Chapter 9 Designing and Deploying Data Models 145
Creating a Data Model Masterpiece 145
Working with Data view and Modeling view 146
Importing queries 149
Defining data types 150
Handling formatting and data type properties 151
Managing tables 153
Adding and modifying data to imported, DirectQuery, and composite models 158
Managing Relationships 159
Creating automatic relationships 159
Creating manual relationships 160
Deleting relationships 160
Classifying and codifying data in tables 161
Arranging Data 162
Sorting by and grouping by 162
Hiding data 162
Working with Extended Data Models 164
Knowing the calculation types 164
Working with column contents and joins 165
Publishing Data Models 166
Chapter 10 Perfecting the Data Model 167
Matching Queries with Capacity 168
Deleting unnecessary columns and rows 168
Swapping numeric columns with measures and variables 169
Reducing cardinality 170
Reducing queries 172
Converting to a composite model 173
Creating and managing aggregations 174
Chapter 11 Visualizing Data 183
Looking at Report Fundamentals and Visualizations 183
Creating visualizations 184
Choosing a visualization 185
Filtering data 185
Working with Bar charts and Column charts 188
Using basic Line charts and Area charts 193
Combining Line charts and Bar charts 193
Working with Ribbon charts 195
Going with the flow with Waterfall charts 195
Funneling with Funnel charts 197
Scattering with Scatter charts 198
Salivating with Pie charts and Donut charts 198
Branching out with treemaps 199
Mapping with maps 200
Indicating with indicators 201
Dealing with Table-Based and Complex Visualizations 205
Slicing with slicers 205
Tabling with table visualizations 205
Combing through data with matrices 206
Decomposing with decomposition trees 206
Zooming in on key influencers 207
Dabbling in Data Science 208
Questions and Answers 210
Chapter 12 Pumping Out Reports 213
Formatting and Configuring Report Visualizations 213
Working with basic visualization configurations 215
Applying conditional formatting 220
Filtering and Sorting 221
Configuring the Report Page 223
Refreshing Data 224
Working with reports 225
Finding migrated data 226
Exporting reports 228
Perfecting reports for distribution 229
Chapter 13 Diving into Dashboarding 233
Configuring Dashboards 234
Creating a New Dashboard 234
Enriching Your Dashboard with Content 236
Pinning Reports 238
Customizing with Themes 240
Working with Dashboard Layouts 241
Integrating Q&A 243
Setting Alerts 244
Part 4 Oh, No! There's a Power BI Programming Language! 247
Chapter 14 Digging Into DAX 249
Discovering DAX 249
Peeking under the DAX hood 250
Working with calculations 253
Dealing with Data Types 258
Operating with Operators 260
Ordering operators 262
Parentheses and order 262
Making a Statement 263
Ensuring Compatibility 263
Chapter 15 Fun with DAX Functions 265
Working with DAX Parameters and Naming Conventions 265
Prefixing parameter names 266
Playing with parameters 267
Using Formulas and Functions 267
Aggregate functions 268
Date-and-time functions 269
Filter functions 271
Financial functions 271
Information functions 274
Logical functions 276
Mathematical and trigonometric functions 277
Other functions 279
Parent-child functions 279
Relationship functions 280
Statistical functions 280
Table manipulation functions 283
Text functions 285
Time intelligence functions 286
Chapter 16 Digging Deeper into DAX 289
Working with Variables 289
Writing DAX Formulas 290
Understanding DAX formulas in depth 290
Extending formulas with measures 290
Comparing measures and columns 296
Syntax and context 296
The syntax of an expression 297
Best Practices for DAX Coding and Debugging in Power BI 297
Using error functions properly 298
Avoiding converting blanks to values 298
Knowing the difference between operators and functions 300
Getting specific 301
Knowing what to COUNT 302
Relationships matter 303
Keeping up with the context 303
Preferring measures over columns 303
Seeing that structure matters 304
Chapter 17 Sharing and the Power BI Workspace 305
Working Together in a Workspace 305
Defining the types of workspaces 306
Figuring out the nuts and bolts of workspaces 308
Creating and Configuring Apps 313
Slicing and Dicing Data 314
Analyzing in Excel 316
Benefiting from Quick Insights 316
Using Usage Metric reports 317
Working with paginated reports 318
Troubleshooting the Use of Data Lineage 318
Datasets, Dataflows, and Lineage 321
Defending Your Data Turf 322
Part 5 Enhancing Your Power BI Experience 325
Chapter 18 Making Your Data Shine 327
Establishing a Schedule 327
Rolling out the scheduled refresh 328
Refreshing on-premises data 329
Protecting the Data Fortress 331
Configuring for group membership 331
Making role assignments in Power BI Services 333
Sharing the Data Love 334
Refreshing Data in Baby Steps 335
Creating RangeStart and RangeEnd parameters 335
Filtering by RangeStart and RangeEnd 336
Establishing the Incremental Refresh policy 338
Treating Data Like Gold 339
Configuring for Big Data 341
Chapter 19 Extending the Power BI Experience 343
Linking Power Platform and Power BI 343
Powering Up with Power Apps 344
Creating Power App visuals with Power BI 346
Acknowledging the limitations of Power Apps/Power BI integration 350
Introducing the Power BI Mobile app 350
Integrating OneDrive and Power BI 351
Collaboration, SharePoint, and Power BI 354
Differentiating between the classic and modern SharePoint experience 354
Integrating Power BI into SharePoint 365 355
Viewing Power BI reports in SharePoint 356
Automating Workflows with Power BI 358
Configuring prebuilt workflows for Power BI 359
Using the Power Automate Visual with Power BI 362
Unleashing Dynamics 365 for Data Analytics 364
Part 6 The Part of Tens 367
Chapter 20 Ten Ways to Optimize DAX Using Power BI 369
Focusing on Logic 369
Formatting Your Code 370
Keeping the Structure Simple (KISS) 371
Staying Clear of Certain Functions 372
Making Your Measures Meaningful 373
Filtering with a Purpose 374
Transforming Data Purposefully 374
Playing Hide-and-Seek with Your Columns 375
Using All Those Fabulous Functions 376
Rinse, Repeat, Recycle 376
Chapter 21 Ten Ways to Make Compelling Reports Accessible and User-Friendly 379
Navigating the Keyboard 380
Having a Screen Reader As Your Companion 380
Standing Out with Contrast 380
Recognizing Size Matters (with Focus Mode) 381
Switching between Data Tables and Visualizations 382
A Little Extra Text Goes a Long Way 383
Setting Rank and Tab Order 384
It's All About Titles and Labels 384
Leaving Your Markers 386
Keeping with a Theme 387
Index 389
