Discover the utility of your organization’s data with Microsoft Power BI
In Beginning Power BI for Business Users: Learning to Turn Data into Insights, accomplished data professional and business intelligence expert Paul Fuller delivers an intuitive and accessible handbook for professionals seeking to use Microsoft’s Power BI to access, analyze, understand, report, and act on the data available to their organizations. In the book, you’ll discover Power BI’s robust feature set, learn to ingest and model data, visualize and report on that data, and even use the DAX scripting language to unlock still more utility from Microsoft’s popular program.
Beginning with general principles geared to readers with no or little experience with reporting or data analytics tools, the author walks you through how to manipulate common, publicly available data sources - including Excel files and relational databases. You’ll also learn to:
- Use the included and tested sample code to work through the helpful examples included by the author
- Conduct data orchestration and visualization to better understand and gain insights from your data
An essential resource for business analysts and Excel power users reaching the limits of that program’s capabilities, Beginning Power BI for Business Users will also benefit data analysts who seek to prepare reports for their organizations using Microsoft’s flexible and intuitive software.
Table of Contents
Introduction xxi
Part 1: Introduction
Chapter 1: Introducing Power BI 3
What Is Power BI? 3
Why Would I Use Power BI? 7
But Why? 7
Disconnected Data 8
Fixed Formulas 9
Ten Reasons Why Power BI Transforms Your Work 10
Will I Still Use Excel? 12
How Much Is this Going to Cost? 14
Where Do We Go from Here? 16
Chapter 2: From Data to Insight 17
Three Guiding Principles 17
Distinguishing Data, Information, and Knowledge 18
Follow a Consistent, Proven Method 21
Data Sources 21
Files 21
Databases 22
Online Services 23
Plan Ahead 23
Import Data 25
Prepare Data 26
Model Data 28
Report Data 29
Analyze Data 30
Use Visualizations Well 31
Three Ways to Ruin Visual Analytics 32
Use as Many Cool Gadgets as You Can Find 32
Overwhelm the User with Tons of Detail 32
Pick the Wrong Tool for the Job 34
Four Ways to Improve Visual Analytics 35
Reduce the Nondata Pixels 35
Enhance the Data Pixels 36
Conclusion 38
Part 2: Power BI Guided Walk-Throughs
Chapter 3: Let’s Take a Flyover! 41
Getting Connected 41
A Quick Tour of the Power BI Service 42
Frequented Hotspots of Power BI 42
Adding Sample Content 43
Understanding the Power BI Service Interface 44
Highlighting 47
Slicers 50
Filters 52
Drill-Down 54
Dashboards 55
Drill-Through 58
Visibility 59
Show as Table 59
Sorting Visuals 60
Filtering - One More Time 61
Power BI Building Blocks 61
Flyover of Power BI Desktop 62
Navigating Power BI Desktop 63
Conclusion 65
Chapter 4: Building Your First Report 67
Connecting to a Data Source 67
Examining the Data Model 68
Exploring the Data 75
Building Your Report 85
Designing for Mobile Devices 101
Conclusion 102
Chapter 5: Preparing Data 103
GDP Data 103
Power Query Overview 105
Data Arrangement Steps 106
Rename Query 106
Remove Rows 106
Remove Columns 107
Applied Steps So Far 107
Promote Headers 108
Renaming Columns 108
Empty Values 109
Handling Aggregated Data 109
Filtering Rows 110
Unpivoting Data 110
Change Column Data Type 112
Changing Values in Columns 112
Duplicating Columns 113
Multiply by a Million 113
Removing Implicit Measures 114
Column Formatting 115
Calculated Columns 115
Add Previous Year Column 116
Hiding Columns 117
LOOKUPVALUE Function 118
DIVIDE Function 119
Visualize Our GDP Data 120
Where Are We? 121
Cleaning Messy Data 122
Handle Many-to-Many Relationships 123
Duplicate a Query 125
Split Column 125
Format Column 127
Remove Duplicates 127
Index Column 128
Merge Data 128
Applying Changes 129
Finding the Power Query Button 131
Simple Cleanup Tasks 131
Replacing Values 132
Custom Columns 133
More Cleanup 135
Cleaning Date Columns 136
Conclusion 138
Chapter 6: Modeling Data 139
Dimensional Modeling 140
Why Does This Matter? 143
Makes Sense to You 143
Cross-Business Process Analysis 144
Flexible Grouping 144
Performance for Power BI 144
Facts 145
Types of Facts 146
Granularity 146
Relationships Between Facts (Never!) 147
Dimensions 148
Using Integer Keys for Relationships 149
Hierarchies in Dimensions 149
Date Dimensions 150
Power Query and Data Modeling 151
How to Build a Date Dimension 152
Import a Date Table from SQL Server Database 153
Create a Date Dimension with Power Query 155
Create a Date Dimension with DAX 158
Sort by Column 161
Mark It as a Date Table 161
How to Arrange into a Star- Schema 161
Create a State Dimension 162
Create a GDP Category Dimension 163
Add a New StateID Foreign Key to Our Fact Table 163
Unpivot the GDP Year Values 164
Putting It All into Action 166
Conclusion 167
Part 3: Going Deeper Into Power BI
Chapter 7: Relationships and Filtering 171
Filtering Through Relationships 171
Visuals Interact Independently 174
The Direction of Relationships 176
Bidirectional Filtering 179
Report Filter Types 181
Visual Filters 181
Date Filtering 182
Numeric Filtering 182
Text Filtering 183
Page and Report Filters 185
Controlling the Use of Filters 185
Conclusion 186
Chapter 8: Enhancing Your Report 187
Configuring Drill- Down 187
Configuring Drill- Through 190
Buttons 193
Bookmarks 195
Personal Bookmarks vs. Report Bookmarks 199
Selections and Visibility 201
Conclusion 205
Chapter 9: Refreshing, Sharing, and Collaborating 207
Refreshing Data 207
Configuring Data Source Credentials 210
Power BI Data Gateway 213
Scheduling Refresh 214
Dataset Modes 216
Sharing and Collaborating 216
Workspaces 216
Creating Workspaces 217
Publishing 217
Security and Permission Levels 219
Power BI Apps 223
Sharing 230
Summary 232
Chapter 10: Introducing Dax 233
Setup 233
Calculated Columns 235
How to Create a Calculated Column 236
DAX Measures 240
COUNTROWS and DISTINCTCOUNT 244
Filter Context 246
CALCULATE Function 249
Conclusion 250
Chapter 11: Conclusion 251
Where Does My Organization Begin? 251
Delivery Strategy 251
Business-Led Self-Service 252
IT-Managed Self-Service 253
Corporate BI 253
Which Delivery Strategy Is Right for My Organization? 253
Where Do I Go from Here? 254
Top Gurus 254
Advice 254
Index 257
