Beginning Power BI for Business Users

PAUL D. FULLER has over 25 years’ experience in app development, business intelligence, and data warehousing. He offers technical leadership and delivery oversight for complex and challenging data platform projects. He is an expert in Power BI, dimensional data modeling, and data warehousing.

• Introduction xxiPart 1: IntroductionChapter 1: Introducing Power BI 3What Is Power BI? 3Why Would I Use Power BI? 7But Why? 7Disconnected Data 8Fixed Formulas 9Ten Reasons Why Power BI Transforms Your Work 10Will I Still Use Excel? 12How Much Is this Going to Cost? 14Where Do We Go from Here? 16Chapter 2: From Data to Insight 17Three Guiding Principles 17Distinguishing Data, Information, and Knowledge 18Follow a Consistent, Proven Method 21Data Sources 21Files 21Databases 22Online Services 23Plan Ahead 23Import Data 25Prepare Data 26Model Data 28Report Data 29Analyze Data 30Use Visualizations Well 31Three Ways to Ruin Visual Analytics 32Use as Many Cool Gadgets as You Can Find 32Overwhelm the User with Tons of Detail 32Pick the Wrong Tool for the Job 34Four Ways to Improve Visual Analytics 35Reduce the Nondata Pixels 35Enhance the Data Pixels 36Conclusion 38Part 2: Power BI Guided Walk-ThroughsChapter 3: Let’s Take a Flyover! 41Getting Connected 41A Quick Tour of the Power BI Service 42Frequented Hotspots of Power BI 42Adding Sample Content 43Understanding the Power BI Service Interface 44Highlighting 47Slicers 50Filters 52Drill-Down 54Dashboards 55Drill-Through 58Visibility 59Show as Table 59Sorting Visuals 60Filtering— One More Time 61Power BI Building Blocks 61Flyover of Power BI Desktop 62Navigating Power BI Desktop 63Conclusion 65Chapter 4: Building Your First Report 67Connecting to a Data Source 67Examining the Data Model 68Exploring the Data 75Building Your Report 85Designing for Mobile Devices 101Conclusion 102Chapter 5: Preparing Data 103GDP Data 103Power Query Overview 105Data Arrangement Steps 106Rename Query 106Remove Rows 106Remove Columns 107Applied Steps So Far 107Promote Headers 108Renaming Columns 108Empty Values 109Handling Aggregated Data 109Filtering Rows 110Unpivoting Data 110Change Column Data Type 112Changing Values in Columns 112Duplicating Columns 113Multiply by a Million 113Removing Implicit Measures 114Column Formatting 115Calculated Columns 115Add Previous Year Column 116Hiding Columns 117LOOKUPVALUE Function 118DIVIDE Function 119Visualize Our GDP Data 120Where Are We? 121Cleaning Messy Data 122Handle Many-to-Many Relationships 123Duplicate a Query 125Split Column 125Format Column 127Remove Duplicates 127Index Column 128Merge Data 128Applying Changes 129Finding the Power Query Button 131Simple Cleanup Tasks 131Replacing Values 132Custom Columns 133More Cleanup 135Cleaning Date Columns 136Conclusion 138Chapter 6: Modeling Data 139Dimensional Modeling 140Why Does This Matter? 143Makes Sense to You 143Cross-Business Process Analysis 144Flexible Grouping 144Performance for Power BI 144Facts 145Types of Facts 146Granularity 146Relationships Between Facts (Never!) 147Dimensions 148Using Integer Keys for Relationships 149Hierarchies in Dimensions 149Date Dimensions 150Power Query and Data Modeling 151How to Build a Date Dimension 152Import a Date Table from SQL Server Database 153Create a Date Dimension with Power Query 155Create a Date Dimension with DAX 158Sort by Column 161Mark It as a Date Table 161How to Arrange into a Star- Schema 161Create a State Dimension 162Create a GDP Category Dimension 163Add a New StateID Foreign Key to Our Fact Table 163Unpivot the GDP Year Values 164Putting It All into Action 166Conclusion 167Part 3: Going Deeper Into Power BIChapter 7: Relationships and Filtering 171Filtering Through Relationships 171Visuals Interact Independently 174The Direction of Relationships 176Bidirectional Filtering 179Report Filter Types 181Visual Filters 181Date Filtering 182Numeric Filtering 182Text Filtering 183Page and Report Filters 185Controlling the Use of Filters 185Conclusion 186Chapter 8: Enhancing Your Report 187Configuring Drill- Down 187Configuring Drill- Through 190Buttons 193Bookmarks 195Personal Bookmarks vs. Report Bookmarks 199Selections and Visibility 201Conclusion 205Chapter 9: Refreshing, Sharing, and Collaborating 207Refreshing Data 207Configuring Data Source Credentials 210Power BI Data Gateway 213Scheduling Refresh 214Dataset Modes 216Sharing and Collaborating 216Workspaces 216Creating Workspaces 217Publishing 217Security and Permission Levels 219Power BI Apps 223Sharing 230Summary 232Chapter 10: Introducing Dax 233Setup 233Calculated Columns 235How to Create a Calculated Column 236DAX Measures 240COUNTROWS and DISTINCTCOUNT 244Filter Context 246CALCULATE Function 249Conclusion 250Chapter 11: Conclusion 251Where Does My Organization Begin? 251Delivery Strategy 251Business-Led Self-Service 252IT-Managed Self-Service 253Corporate BI 253Which Delivery Strategy Is Right for My Organization? 253Where Do I Go from Here? 254Top Gurus 254Advice 254Index 257