Beginning Power BI (eBook)

Dan Clark is a senior business intelligence (BI)/programming consultant specializing in Microsoft technologies. He is focused on learning new BI/data technologies and on training others how to best implement the technology. Dan has published several books and numerous articles on .NET programming and BI development. He is a regular speaker at various developer/database conferences and user group meetings and enjoys interacting with the Microsoft developer and database communities. In a previous life, he was a physics teacher; he is still inspired by the wonder and awe of studying the universe and figuring out why things behave the way they do. 

Contents at a Glance 4
Contents 6
About the Author 11
About the Technical Reviewer 12
Acknowledgments 13
Introduction 14
Part I: Building Models in Power Pivot 15
Chapter 1: Introducing Power Pivot 16
Why Use Power Pivot? 16
The xVelocity In-memory Analytics Engine 17
Enabling Power Pivot for Excel 18
Exploring the Data Model Manager Interface 21
Summary 31
Chapter 2: Importing Data into Power Pivot 32
Importing Data from Relational Databases 32
Importing Data from Text Files 47
Importing Data from a Data Feed 52
Importing Data from an OLAP Cube 54
Reusing Existing Connections to Update the Model 56
Summary 67
Chapter 3: Data Munging with Power Query 68
Discovering and Importing Data 68
Transforming, Cleansing, and Filtering Data 71
Merging and Shaping Data 74
Grouping and Aggregating Data 78
Inserting Calculated Columns 80
Summary 88
Chapter 4: Creating the Data Model 89
What Is a Data Model? 89
Creating Table Relations 91
Creating a Star Schema 95
Understanding When to Denormalize the Data 97
Creating Linked Tables 98
Creating Hierarchies 100
Making a User-Friendly Model 100
Summary 111
Chapter 5: Creating Calculations with DAX 112
What Is DAX? 112
Implementing DAX Operators 115
Working with Text Functions 116
Using DAX Date and Time Functions 118
Using Informational and Logical Functions 119
Getting Data from Related Tables 120
Using Math, Trig, and Statistical Functions 121
Tips for Creating Calculations in Power Pivot 123
Summary 128
Chapter 6: Creating Measures with DAX 129
Measures vs. Attributes 129
Creating Common Aggregates 131
Mastering Data Context 134
Altering the Query Context 135
Using Filter Functions 137
Creating KPIs 142
Summary 153
Chapter 7: Incorporating Time Intelligence 154
Date-Based Analysis 154
Creating a Date Table 156
Time Period–Based Evaluations 159
Shifting the Date Context 160
Using Single Date Functions 164
Creating Semi-Additive Measures 165
Summary 173
Chapter 8: Data Analysis with Pivot Tables and Charts 174
Pivot Table Fundamentals 174
Slicing the Data 177
Adding Visualizations to a Pivot Table 179
Working with Pivot Charts 183
Using Multiple Charts and Tables 187
Using Cube Functions 189
Summary 198
Part II: Building Interactive Reports and Dashboards with Power BI Desktop 199
Chapter 9: Introducing Power BI Desktop 200
Setting Up the Power BI Environment 200
Getting, Cleaning, and Shaping Data 203
Creating Table Relationships 209
Adding Calculations and Measures 214
Incorporating Time-based Analysis 217
Summary 223
Chapter 10: Creating Reports with Power BI Desktop 224
Creating Tables and Matrices 224
Constructing Bar, Column, and Pie Charts 229
Building Line and Scatter Charts 236
Creating Map-Based Visualizations 241
Linking Visualizations in Power BI 246
Drilling Through Visualizations 250
Summary 256
Chapter 11: Publishing Reports and Creating Dashboards in the Power BI Portal 257
Creating a User-Friendly Model 257
Publishing Power BI Desktop Files to the Power BI Service 260
Adding Tiles to a Dashboard 263
Sharing Dashboards 268
Refreshing Data in Published Reports 272
Summary 287
Chapter 12: Creating a Complete Solution 288
Use Case 1: Reseller Sales Analysis 288
Load the Data 288
Create the Model 289
Create Calculated Columns 291
Create Measures 291
Create the Dashboard 293
Use Case 2: Sales Quota Analysis 295
Load the Data 295
Create the Model 299
Create Measures 300
Create the Report 301
Use Case 3: Sensor Analysis 302
Load the Data 303
Summary 305
Chapter 13: Advanced Topics in Power Query 306
Writing Queries with M 306
Creating and Using Parameters 309
Creating and Using Functions 314
Summary 323
Chapter 14: Advanced Topics in Power BI 324
Using Custom Visuals 324
Implementing Geo-spatial Analysis 328
Implementing Row-Based Security 333
Creating Templates and Content Packs 335
Summary 347
Index 348