Microsoft Power BI Complete Reference

Microsoft Power BI Complete Reference

by Devin Knight, Brian Knight, Mitchell Pearson, Manuel Quintana
Released December 2018
Publisher(s): Packt Publishing
ISBN: 9781789950045

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Design, develop, and master efficient Power BI solutions for impactful business insights

Key Features

  • Get to grips with the fundamentals of Microsoft Power BI
  • Combine data from multiple sources, create visuals, and publish reports across platforms
  • Understand Power BI concepts with real-world use cases

Book Description

Microsoft Power BI Complete Reference Guide gets you started with business intelligence by showing you how to install the Power BI toolset, design effective data models, and build basic dashboards and visualizations that make your data come to life.

In this Learning Path, you will learn to create powerful interactive reports by visualizing your data and learn visualization styles, tips and tricks to bring your data to life. You will be able to administer your organization's Power BI environment to create and share dashboards. You will also be able to streamline deployment by implementing security and regular data refreshes.

Next, you will delve deeper into the nuances of Power BI and handling projects. You will get acquainted with planning a Power BI project, development, and distribution of content, and deployment. You will learn to connect and extract data from various sources to create robust datasets, reports, and dashboards. Additionally, you will learn how to format reports and apply custom visuals, animation and analytics to further refine your data.

By the end of this Learning Path, you will learn to implement the various Power BI tools such as on-premises gateway together along with staging and securely distributing content via apps.

This Learning Path includes content from the following Packt products:

  • Microsoft Power BI Quick Start Guide by Devin Knight et al.
  • Mastering Microsoft Power BI by Brett Powell

What you will learn

  • Connect to data sources using both import and DirectQuery options
  • Leverage built-in and custom visuals to design effective reports
  • Administer a Power BI cloud tenant for your organization
  • Deploy your Power BI Desktop files into the Power BI Report Server
  • Build efficient data retrieval and transformation processes

Who this book is for

Microsoft Power BI Complete Reference Guide is for those who want to learn and use the Power BI features to extract maximum information and make intelligent decisions that boost their business. If you have a basic understanding of BI concepts and want to learn how to apply them using Microsoft Power BI, then Learning Path is for you. It consists of real-world examples on Power BI and goes deep into the technical issues, covers additional protocols, and much more.

Publisher resources

View/Submit Errata

Table of contents

  1. Title Page
  2. Copyright
    1. Microsoft Power BI Complete Reference
  3. About Packt
    1. Why subscribe?
    2. PacktPub.com
  4. Contributors
    1. About the authors
    2. About the reviewers
    3. Packt is searching for authors like you
  5. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the example code files
      2. Conventions used
    4. Get in touch
      1. Reviews
  6. Getting Started with Importing Data Options
    1. Getting started
    2. Importing data
      1. Excel as a source
      2. SQL Server as a source
      3. Web as a source
    3. DirectQuery
      1. Limitations
    4. Live Connection
      1. Limitations
    5. Which should I choose?
    6. Summary
  7. Data Transformation Strategies
    1. The Power Query Editor
    2. Transform basics
      1. Use First Row as Headers
      2. Remove Columns
      3. Change type
      4. Add Column From Examples
    3. Advanced data transformation options
      1. Conditional Columns
      2. Fill Down
      3. Unpivot
      4. Merging Queries
      5. Appending Queries
    4. Leveraging R
      1. Installation and configuration
      2. The R Script transform
    5. M formula language
      1. #shared
    6. Summary
  8. Building the Data Model
    1. Building relationships
      1. Editing relationships
      2. Creating a new relationship 
    2. Working with complex relationships
      1. Many-to-many relationships
        1. Cross-filtering direction
        2. Enabling filtering from the many side of a relationship
      2. Role-playing tables
        1. Importing the date table
    3. Usability enhancements
      1. Hiding tables and columns
      2. Renaming tables and columns
      3. Default summarization
      4. How to display one column but sort by another
      5. Data categorization
      6. Creating hierarchies
    4. Summary
  9. Leveraging DAX
    1. Building calculated columns
      1. String functions – Month, Year
      2. Format function – Month Year
      3. Age calculation
      4. SWITCH() – age breakdown
      5. Navigation functions – RELATED
    2. Calculated measures – the basics
      1. Calculated measure – basic aggregations
        1. Total Sales
        2. Total Cost
        3. Profit
        4. Profit Margin
          1. Optional parameters
      2. Filter context
        1. Calculate
        2. Percentage of total calculation
        3. Time intelligence
          1. Year to Date Sales
          2. YTD Sales (Fiscal Calendar)
          3. Prior Year Sales 
    3. Summary
  10. Visualizing Data
    1. Data visualization basics
    2. Visuals for filtering
      1. Interactive filtering
      2. The Slicer visual
    3. Visualizing tabular data
      1. The table visual
      2. The Matrix visual
    4. Visualizing categorical data
      1. Bar and Column charts
      2. Pie and Donut charts
      3. The Treemap visual
      4. The Scatter chart
    5. Visualizing trend data
      1. Line and Area charts
      2. Combo charts
      3. The Ribbon Chart
      4. The Waterfall Chart
      5. The Funnel Chart
    6. Visualizing KPI data
      1. The Gauge visual
      2. The KPI visual
    7. Visualizing geographical data
      1. The Map visual
      2. The Filled Map visual
      3. The Shape Map visual
      4. The ArcGIS Map visual
    8. Leveraging Power BI custom visuals
    9. Data visualization tips and tricks
      1. Edit interactions
      2. The Analytics pane
      3. The Top N filter
      4. Show value as
    10. Summary
  11. Using a Cloud Deployment with the Power BI Service
    1. Deploying to the Power BI service
      1. DATASETS
      2. WORKBOOKS
    2. Creating and interacting with dashboards
      1. Creating your first dashboard
      2. Asking your dashboard a question
      3. Subscribing to reports and dashboards
    3. Sharing your dashboards
      1. Workspaces
    4. Setting up row-level security
    5. Scheduling data refreshes
    6. Summary
  12. Planning Power BI Projects
    1. Power BI deployment modes
      1. Corporate BI
      2. Self-Service Visualization
      3. Self-Service BI
      4. Choosing a deployment mode
    2. Project discovery and ingestion
      1. Sample Power BI project template 
        1. Sample template – Adventure Works BI
    3. Power BI project roles
      1. Dataset designer
      2. Report authors
      3. Power BI admin
      4. Project role collaboration
    4. Power BI licenses
      1. Power BI license scenarios
        1. Power BI Premium features
    5. Data warehouse bus matrix
    6. Dataset design process
      1. Selecting the business process
      2. Declaring the grain
      3. Identifying the dimensions
      4. Defining the facts
    7. Data profiling
    8. Dataset planning
      1. Data transformations
        1. DirectQuery mode
    9. Sample project analysis
    10. Summary
  13. Connecting to Sources and Transforming Data with M
    1. Query design per dataset mode
      1. Import mode dataset queries
      2. DirectQuery dataset queries
    2. Data sources
      1. Authentication
      2. Data source settings
      3. Privacy levels
      4. Power BI as a data source
      5. Power BI Desktop options
        1. Global options
        2. CURRENT FILE options
    3. SQL views
      1. SQL views versus M queries
      2. SQL view examples
        1. Date dimension view
        2. Mark As Date Table
        3. Product Dimension view
      3. Slowly-changing dimensions
    4. M queries
      1. Data Source Parameters
      2. Staging Queries
        1. DirectQuery staging
      3. Fact and dimension queries
        1. Source Reference Only
      4. M query summary
        1. Excel workbook – Annual Sales Plan
      5. Data types
        1. Item access in M
        2. DirectQuery report execution
      6. Bridge Tables Queries
      7. Parameter Tables
      8. Security Tables
    5. Query folding
      1. Partial query folding
    6. M Query examples
      1. Trailing three years filter
      2. Customer history column
        1. Derived column data types
      3. Product dimension integration
    7. M editing tools
      1. Advanced Editor
      2. Visual Studio Code
      3. Visual Studio
    8. Summary
  14. Designing Import and DirectQuery Data Models
    1. Dataset layers
      1. Dataset objectives
        1. Competing objectives
        2. External factors
    2. The Data Model 
      1. The Relationships View
      2. The Data View
      3. The Report View
      4. Fact tables
        1. Fact table columns
        2. Fact column data types
        3. Fact-to-dimension relationships
      5. Bridge tables
      6. Parameter tables
        1. Measure groups
        2. Last refreshed date
        3. Measure support logic
    3. Relationships
      1. Uniqueness 
      2. Ambiguity
    4. Model metadata
      1. Visibility
      2. Column metadata
        1. Default Summarization
        2. Data format
        3. Data category
      3. Field descriptions
    5. Optimizing performance
      1. Import
        1. Columnar compression
        2. Memory analysis via DMVs
      2. DirectQuery 
        1. Optimized DAX functions
        2. Columnstore and HTAP
    6. Summary
  15. Developing DAX Measures and Security Roles
    1. DAX measures
      1. Measure evaluation process
      2. Row context
      3. Scalar and table functions
      4. The CALCULATE() function
        1. Related tables
        2. The FILTER() function
      5. DAX variables
    2. Base measures
      1. Measure support expressions
        1. KPI Targets
        2. Current and prior periods
    3. Date intelligence metrics
      1. Current versus prior and growth rates
      2. Rolling periods
    4. Dimension metrics
      1. Missing dimensions
    5. Ranking metrics
      1. Dynamic ranking measures
    6. Security roles
      1. Dynamic row-level security
    7. Performance testing
      1. DAX Studio
        1. Tracing a Power BI dataset via DAX Studio
    8. Summary
  16. Creating and Formatting Power BI Reports
    1. Report planning
      1. Power BI report architecture
    2. Live connections to Power BI datasets
      1. Customizing Live connection reports
      2. Switching source datasets
    3. Visualization best practices
      1. Visualization anti-patterns
    4. Choosing the visual
      1. Tables versus charts
      2. Chart selection
    5. Visual interactions
      1. Edit interactions
      2. What-if parameters
    6. Slicers
      1. Slicer synchronization
      2. Custom slicer parameters
    7. Report filter scopes
      1. Report filter conditions
      2. Report and page filters
        1. Page filter or slicer?
      3. Relative date filtering
    8. Visualization formatting
      1. Visual-level formatting
      2. Line and column charts
      3. Tooltips
        1. Report page tooltips
      4. Column and line chart conditional formatting
        1. Column chart conditional formatting
        2. Line chart conditional formatting
      5. Table and matrix
        1. Table and matrix conditional formatting
        2. Values as rows
      6. Scatter charts
    9. Map visuals
      1. Bubble map
      2. Filled map
    10. Mobile-optimized reports
      1. Responsive visuals
    11. Report design summary
    12. Summary
  17. Applying Custom Visuals, Animation, and Analytics
    1. Drillthrough report pages
      1. Custom labels and the back button
      2. Multi-column drillthrough
    2. Bookmarks
      1. Selection pane and the Spotlight property
      2. Custom report navigation
      3. View mode
    3. ArcGIS Map visual for Power BI
      1. ArcGIS Maps Plus subscriptions
    4. Waterfall chart breakdown
    5. Analytics pane
      1. Trend Line
      2. Forecast line
    6. Quick Insights 
      1. Explain the increase/decrease
    7. Custom visuals
      1. Adding a custom visual
      2. Power KPI visual
      3. Chiclet Slicer
      4. Impact Bubble Chart
      5. Dot Plot by Maq Software
    8. Animation and data storytelling
      1. Play axis for scatter charts
      2. Pulse Chart
    9. Summary
  18. Designing Power BI Dashboards and Architectures
    1. Dashboards versus reports
    2. Multi-dashboard architectures
      1. Single-dashboard architecture
      2. Multiple-dashboard architecture
      3. Organizational dashboard architecture
      4. Multiple datasets
    3. Dashboard tiles
      1. Tile details and custom links
      2. Images and text boxes
      3. SQL Server Reporting Services
      4. Excel workbooks
    4. Live report pages
    5. Mobile-optimized dashboards
    6. Summary
  19. Managing Application Workspaces and Content
    1. Application workspaces
      1. Workspace roles and rights
        1. Workspace admins
        2. Workspace members
      2. My Workspace
    2. Staged deployments
      1. Workspace datasets
      2. Power BI REST API
        1. Client application ID
        2. Workspace and content IDs
        3. PowerShell sample scripts
    3. Dashboard data classifications
    4. Version control
      1. OneDrive for Business version history
      2. Source control for M and DAX code
    5. Metadata management
      1. Field descriptions
        1. Creating descriptions
        2. View field descriptions
      2. Metadata reporting
        1. Query field descriptions
        2. Standard metadata reports
          1. Server and database parameters
          2. Querying the DMVs from Power BI
          3. Integrating and enhancing DMV data
          4. Metadata report pages
    6. Summary
  20. Managing the On-Premises Data Gateway
    1. On-premises data gateway planning
      1. Top gateway planning tasks
        1. Determining whether a gateway is needed
        2. Identifying where the gateway should be installed
        3. Defining the gateway infrastructure and hardware requirements
      2. On-premises data gateway versus personal mode
    2. Gateway clusters
    3. Gateway architectures
    4. Gateway security
    5. Gateway configuration
      1. The gateway service account
      2. TCP versus HTTPS mode 
    6. Managing gateway clusters
      1. Gateway administrators
      2. Gateway data sources and users
      3. PowerShell support for gateway clusters
    7. Troubleshooting and monitoring gateways
      1. Restoring, migrating, and taking over a gateway
      2. Gateway log files
      3. Performance Monitor counters
    8. DirectQuery datasets
      1. Single sign-on to DirectQuery sources via Kerberos
    9. Live connections to Analysis Services models
      1. Azure Analysis Services refresh
    10. Dashboard cache refresh
    11. Summary
  21. Deploying the Power BI Report Server
    1. Planning for the Power BI Report Server
      1. Feature differences with the Power BI service
      2. Parity with SQL Server Reporting Services
      3. Data sources and connectivity options
      4. Hardware and user licensing
        1. Pro licenses for report authors
      5. Alternative and hybrid deployment models
      6. Report Server reference topology
    2. Installation
      1. Hardware and software requirements
        1. Analysis Services Integrated
      2. Retrieve the Report Server product key
      3. Migrating from SQL Server Reporting Services
    3. Configuration
      1. Service Account
      2. Remote Report Server Database
      3. Office Online Server for Excel Workbooks
    4. Upgrade cycles 
    5. Report Server Desktop Application
      1. Running desktop versions side by side
    6. Report Server Web Portal
      1. Scheduled data refresh
      2. Data source authentication
    7. Power BI mobile applications
    8. Report server administration
      1. Securing Power BI report content
      2. Execution logs
    9. Scale Power BI Report Server
    10. Summary
  22. Creating Power BI Apps and Content Distribution
    1. Content distribution methods
    2. Power BI apps
      1. Licensing apps
      2. App deployment process
      3. User permissions
      4. Publishing apps
      5. Installing apps
      6. Apps on Power BI mobile
      7. App updates
      8. Dataset-to-workspace relationship
    3. Self-Service BI workspace
      1. Self-Service content distribution
      2. Risks to Self-Service BI
    4. Sharing dashboards and reports
      1. Sharing scopes
      2. Sharing versus Power BI apps
    5. SharePoint Online embedding
    6. Custom application embedding
    7. Publish to web
    8. Data alerts
      1. Microsoft Flow integration
    9. Email Subscriptions
    10. Analyze in Excel
      1. Power BI Publisher for Excel
    11. Summary
  23. Administering Power BI for an Organization
    1. Data governance for Power BI
      1. Implementing data governance
    2. Azure Active Directory
      1. Azure AD B2B collaboration
        1. Licensing external users
      2. Conditional access policies
    3. Power BI Admin Portal
      1. Usage metrics
      2. Users and Audit logs
      3. Tenant settings
      4. Embed Codes
      5. Organizational Custom visuals
    4. Usage metrics reports
    5. Audit logs
      1. Audit log monitoring solutions
      2. Audit logs solution template
    6. Power BI Premium capacities
      1. Capacity allocation
      2. Create, size, and monitor capacities
        1. Change capacity size
        2. Monitor premium capacities
      3. App workspace assignment
      4. Capacity admins
    7. Summary
  24. Scaling with Premium and Analysis Services
    1. Power BI Premium
      1. Power BI Premium capabilities
      2. Corporate Power BI datasets
        1. Limitation of Corporate BI datasets – Reusability
      3. Premium capacity nodes
        1. Frontend versus backend resources
    2. Power BI Premium capacity allocation
      1. Corporate and Self-Service BI capacity 
      2. Power BI Premium resource utilization
        1. Data model optimizations
        2. Report and visualization optimizations
    3. Premium capacity estimations
    4. Analysis Services
      1. Analysis Services Models versus Power BI Desktop
        1. Scale
        2. Usability
        3. Development and management tools
      2. Azure Analysis Services versus SSAS
        1. SSAS to Azure AS Migration
      3. Provision Azure Analysis Services 
    5. Migration of Power BI Desktop to Analysis Services
    6. Summary
  25. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: Microsoft Power BI Complete Reference
  • Author(s): Devin Knight, Brian Knight, Mitchell Pearson, Manuel Quintana
  • Release date: December 2018
  • Publisher(s): Packt Publishing
  • ISBN: 9781789950045