Mastering Power BI, 2nd Edition

✍ Scribed by Chandraish Sinha

• Publisher: BPB Publications
• Year: 2024
• Tongue: English
• Leaves: 280
• Edition: 2
• Category: Library

✦ Synopsis

Mastering Power BI covers the entire Power BI implementation process. The readers will be able to understand all the concepts covered in this book, from data modeling to creating powerful visualizations.

This book begins with concepts and terminology such as the star-schema, dimensions, and facts. It explains multi-table dataset and demonstrates how to load these tables into Power BI. It shows how to load stored data in various formats and create relationships. Readers will also learn more about Data Analysis Expressions (DAX). This book is a must for developers to learn how to extend the usability of Power BI, to explore meaningful and hidden data insights. Throughout the book, you keep on learning about the concepts, techniques, and expert practices on loading and shaping data, visualization design, and security implementation.

The second edition of Mastering Power BI book adheres to the first edition in terms of providing the basics of business intelligence and Power BI;...

✦ Table of Contents

Cover Page
Title Page
Copyright Page
Dedication
About the Author
About the Reviewers
Acknowledgement
Preface
Table of Contents
1. Understanding the Basics
Introduction
Structure
Objectives
Understanding business intelligence
Advantages of BI system
Components of BI
Data sets
Extract, Transform, and Load
Data warehouse
Data mart
Data model
Concepts of the star and snowflake schema
Star schema
Snowflake schema
Key performance indicator
Visualization
Dashboard
Power BI as a business intelligence application
Functions of Power BI
Power BI components
Power BI environment
Different users of Power BI
Power BI desktop developer
Power BI analyst
Power User
Executive user
Power BI licensing
Installation of Power BI desktop
Installation
Configuring Power BI desktop options and settings
Power BI desktop interface
Development life cycle in Power BI desktop
Initiate a Power BI implementation as a developer
Overview of the data used in this book
Power BI development setup for learning
Conclusion
Questions
Answers
2. Connect and Shape
Introduction
Structure
Objectives
Data connections in Power BI
Connecting to data
Connecting to database tables
Loading the table
Understanding Power Query Editor
Verifying the loaded query
Changing the default summarization
Changing the display format of the numbers
Review your model
Loading customers and products tables
Verifying the loaded query
Changing the default summarization for the Products table
Review your model
Loading data from an Excel file
Loading Category data from an Excel file
Loading multiple CSV files from a folder
Loading data from MS SQL server
Import and DirectQuery
Creating static table in Power BI
The need for a static table in the model
Review the data model
Bonus section
Connecting to web data source
Loading data from SharePoint
Connecting to SharePoint using ODataFeed
Connecting to the SharePoint Lists using the SharePoint connector
Useful transformations using Query Editor
Applying Group By to CustomerSales_Report
Group By to calculate Total by Customer
Advanced group by to calculate totals
Creating a Column in Query Editor
Conclusion
Questions
Answers
3. Advanced Data Transformations
Introduction
Structure
Objectives
Data profiling using Query Editor
Data profiling
Data profiling options in Query Editor
Performing data profiling on a table
Validating Column quality
Understanding the errors reported by Column quality
Validating column distribution
Caution before taking Query Editor suggestion
Validating column profile
Data transformation on text data
Trim leading and trailing spaces
Delimiter option to create a new column
Using the Merge Columns option
Data transformation on Numeric Data
The statistics option
The standard option
Data transformation on Dates
The gear symbol in applied steps
Utilizing Data Source parameters
Using a parameter to change the data source
Conclusion
Questions
Answers
4. Optimize Your Data Model
Introduction
Structure
Objectives
Introduction to data modeling
Best practices of data modeling
Review and create relationships
Establishing relationships
Review the loaded tables
Review the orders and products relationship
Manually linking orders and customers table
Manually linking products and category tables
Manually linking orders and sales tables
Review the data model
About model explorer in model view
Combining queries in Power BI
Combining queries using joins
Joins
Loading OrderDetails.xlsx
Left outer join
Right outer join
Full outer join
Left anti
Right anti
Handling Multiple Fact tables
Combining queries using append
Append records from NewCustomers table to Customers
Conclusion
Questions
Answers
5. Data Analysis Expressions
Introduction
Structure
Objectives
Introduction to DAX
DAX functions
Overview of DAX functions
Calculated columns and measures
Calculated columns
Exercise 1
Verify the Unit Price calculation
Exercise 2
Calculated measure
Exercise 3
Quick measures
Creating a quick measure
Exercise 4
Mathematical functions
Sum
Exercise 5
SumX
SumX versus Sum
Using SumX
Exercise 6
Count functions
Count
Exercise 7
CountA
Exercise 8
Using Count and CountA
CountX
CountAX
CountBlank
CountRows
Information functions
ISERROR
Exercise 9
USERNAME
Exercise 10
LOOKUPVALUE
LOOKUPVALUE: SalesPerson_Location and Sales Person
Hiding the redundant tables
Logical functions
IF
Exercise 11
And
Exercise 12
Switch
Exercise 13
Filter functions
Filter
Calculate
Exercise 14
CalculateTable
Exercise 15
Optional example
ALL
Exercise 16
Related
Exercise 17
RelatedTable
Values
Exercise 18
Date and time functions
Calendar
Exercise 19
DateDiff
Exercise 20
Variables in DAX expressions
VAR statement
Exercise 21
Conclusion
Questions
Answers
6. Visualizations in Power BI
Introduction
Structure
Objectives
Review the data model
Understanding visualization
Introduction to Power BI reports
Creating a report
Standardizing report development
Report themes
Applying built-in theme
Creating and using templates
Power BI desktop projects
Creating multi-page reports using visualizations
Remove any pages or visualizations
Creating visualizations
Card
Exercise 1
Copy/paste a visual
Bar and column chart
Exercise 2
Filters and slicers
Line and area chart
Exercise 3
Line and stacked column chart
Exercise 4
Ribbon chart
Exercise 5
Waterfall, funnel, and scatter charts
Exercise 6
Scatter chart
Exercise 7
Pie, donut, and treemap charts
Exercise 8
Treemap
Exercise 9
Highlighting and Cross filtering
Exercise 10
Map
Exercise 11
Conclusion
Questions
Answers
7. Drill Through and Drill Down Reports
Introduction
Structure
Objectives
Drill through report in Power BI
Creating a drill-through report
Exercise
Performing drill through action
Drill down report in Power BI
Exercise
Creating custom hierarchy
Creating a location hierarchy
Exercise
Changing the levels of hierarchy
Exercise
Conclusion
Questions
Answers
8. Artificial Intelligence in Power BI
Introduction
Structure
Objectives
Artificial Intelligence in Power BI
Why AI visuals are used
AI visuals in Power BI desktop
Preparing the application for AI
Insights using Analyze option
Exercise
Key influencers visual
Exercise
Explanation of key influencer visualization
Decomposition tree visual
Exercise
Performing manual analysis using decomposition tree
Performing AI suggested analysis using decomposition tree
Q&A Visual
Exercise
Narrative visual
Working of smart narrative
Exercise
AI in Query Editor
Text Analytics and Vision
Azure Machine Learning
Conclusion
Questions
Answers
9. Power BI Service
Introduction
Structure
Objectives
Understanding the Power BI service
Power BI service workflow
Power BI service interface
Foundational elements of Power BI Service
Publishing reports from the Power BI desktop
Visualizations in Power BI Service
Creating a dashboard
Conclusion
Questions
Answers
10. Securing Your Application
Introduction
Structure
Objectives
Row-level security in Power BI
Implementing Row-Level Security
Exercise
Configure RLS in Power BI service
Conclusion
Questions
Answers
Index