£28.99

Description

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

Back