Beginning Power BI with Excel 2013: Self-Service Business Intelligence Using Power Pivot, Power View, Power Query, and Power Map

Contents at a Glance......Page 3
Contents......Page 302
About the Author......Page 307
About the Technical Reviewers......Page 308
Acknowledgments......Page 309
Introduction......Page 5
Part 1: Building Models in Power Pivot......Page 6
  Why Use Power Pivot?......Page 7
  The xVelocity In-memory Analytics Engine......Page 8
  Enabling Power Pivot for Excel......Page 9
  Exploring the Data Model Manager Interface......Page 12
  Summary......Page 22
  Importing Data from Relational Databases......Page 23
  Importing Data from Text Files......Page 38
  Importing Data from a Data Feed......Page 42
  Importing Data from an OLAP Cube......Page 43
  Reusing Existing Connections to Update the Model......Page 46
  Summary......Page 55
  What Is a Data Model?......Page 56
  Creating Table Relations......Page 58
  Creating a Star Schema......Page 61
  Understanding When to Denormalize the Data......Page 62
  Creating Linked Tables......Page 64
  Making a User-Friendly Model......Page 66
  Summary......Page 73
  What Is DAX?......Page 74
  Implementing DAX Operators......Page 76
  Working with Text Functions......Page 78
  Using DAX Date and Time Functions......Page 79
  Using Informational and Logical Functions......Page 81
  Getting Data from Related Tables......Page 82
  Using Math, Trig, and Statistical Functions......Page 83
  Tips for Creating Calculations in Power Pivot......Page 84
  Summary......Page 89
  Measures versus Attributes......Page 90
  Creating Common Aggregates......Page 92
  Mastering Data Context......Page 95
  Altering the Query Context......Page 96
  Using Filter Functions......Page 98
  Creating KPIs......Page 103
  Summary......Page 114
  Date-Based Analysis......Page 115
  Creating a Date Table......Page 117
  Time Period–Based Evaluations......Page 119
  Shifting the Date Context......Page 120
  Using Single Date Functions......Page 123
  Creating Semi-additive Measures......Page 124
  Summary......Page 134
  Pivot Table Fundamentals......Page 135
  Slicing the Data......Page 137
  Adding Visualizations to a Pivot Table......Page 140
  Working with Pivot Charts......Page 144
  Using Multiple Charts and Tables......Page 147
  Using Cube Functions......Page 150
  Summary......Page 159
Part 2: Building Interactive Reports and Dashboards with Power View......Page 160
  Visualizing Data with Power View......Page 161
  Creating a Basic Report......Page 165
  Improving the Power View Experience......Page 167
  Summary......Page 178
  Creating Tables and Matrices......Page 179
  Constructing Bar, Column, and Pie Charts......Page 184
  Building Line and Scatter Charts......Page 188
  Creating Map-Based Visualizations......Page 191
  Summary......Page 198
  Linking Visualizations in Power View......Page 199
  Using Tiles to Organize the Data......Page 202
  Filtering Groups and Views......Page 205
  Exposing the Dashboard......Page 208
  Summary......Page 214
Part 3: Exploring and Presenting Data with Power Query and Power Map......Page 215
  Discovering and Importing Data......Page 216
  Transforming, Cleansing, and Filtering Data......Page 219
  Merging and Shaping Data......Page 222
  Grouping and Aggregating Data......Page 226
  Inserting Calculated Columns......Page 227
  Summary......Page 233
  Preparing Data for Mapping......Page 234
  Creating a Map-Based Graph......Page 235
  Creating Heat and Region Maps......Page 239
  Adding Multiple Layers to a Power Map......Page 243
  Analyzing Changes over Time......Page 244
  Creating a Tour......Page 245
  Summary......Page 252
  Table Analysis Tools in Excel......Page 253
  Analyzing Key Influencers......Page 256
  Identifying Data Groups......Page 259
  Forecasting Future Trends......Page 263
  Using Shopping Basket Analysis......Page 265
  Summary......Page 272
   Load the Data......Page 273
   Create the Model......Page 274
   Create Measures......Page 276
   Create the Dashboard......Page 279
  Use Case 2: Sales Quota Analysis......Page 282
   Create the Model......Page 283
   Create the Dashboard......Page 286
  Use Case 3: Sensor Analysis......Page 288
   Load the Data......Page 289
  Summary......Page 292
Index......Page 293