Microsoft Power BI Data Analyst Certification Guide: A comprehensive guide to becoming a confident and certified Power BI professional

✍ Scribed by Orrin Edenfield, Edward Corcoran

• Publisher: Packt Publishing
• Year: 2022
• Tongue: English
• Leaves: 399
• Category: Library

✦ Synopsis

Gain the knowledge and skills needed to become a certified Microsoft Power BI data analyst and get the most out of Power BI

Key Features

• Get the skills you need to pass the PL-300 certification exam with confidence
• Create and maintain robust reports and dashboards to enable a data-driven enterprise
• Test your new BI skills with the help of practice questions

Book Description

Microsoft Power BI enables organizations to create a data-driven culture with business intelligence for all. This guide to achieving the Microsoft Power BI Data Analyst Associate certification will help you take control of your organization's data and pass the exam with confidence.

From getting started with Power BI to connecting to data sources, including files, databases, cloud services, and SaaS providers, to using Power BI's built-in tools to build data models and produce visualizations, this book will walk you through everything from setup to preparing for the certification exam. Throughout the chapters, you'll get detailed explanations and learn how to analyze your data, prepare it for consumption by business users, and maintain an enterprise environment in a secure and efficient way.

By the end of this book, you'll be able to create and maintain robust reports and dashboards, enabling you to manage a data-driven enterprise, and be ready to take the PL-300 exam with confidence.

What you will learn

• Connect to and prepare data from a variety of sources
• Clean, transform, and shape your data for analysis
• Create data models that enable insight creation
• Analyze data using Microsoft Power BI's capabilities
• Create visualizations to make analysis easier
• Discover how to deploy and manage Microsoft Power BI assets

Who this book is for

This book is for data analysts and BI professionals who want to become more competent in Microsoft Power BI. Although the content in this book will help you pass the PL-300 exam, there are plenty of other practical applications beyond exam preparation in the chapters. No prior experience with Power BI is needed.

Table of Contents

1. Overview of Power BI and the PL-300 Exam
2. Connecting to Data Sources
3. Profiling the Data
4. Cleansing, Transforming, and Shaping Data
5. Designing a Data Model
6. Using Data Model Advanced Features
7. Creating Measures Using DAX
8. Optimizing Model Performance
9. Creating Reports
10. Creating Dashboards
11. Enhancing Reports
12. Exposing Insights from Data
13. Performing Advanced Analytics
14. Managing Workspaces
15. Managing Datasets
16. Practice Exams

✦ Table of Contents

Cover
Title
Copyright and Credits
Table of Contents
Part 1 – Preparing the Data
Chapter 1: Overview of Power BI and the PL-300 Exam
A brief overview of Power BI
Power BI for business intelligence
Power BI as a solution
Why get certified?
PL-300 Analyzing Data with Microsoft Power BI
Microsoft tests
Timelines
Strategies to get a passing grade
Summary
Questions
Chapter 2: Connecting to Data Sources
Technical requirements
Identifying data sources
Local data sources, files, and databases
Cloud and SaaS data sources
Connecting to data sources
On-premises data gateway
Exploring query types
Power BI datasets
Power BI dataflows
Query performance tuning
Reducing the data size
DirectQuery optimization
Composite model optimization
Advanced options (what-if parameters, Power Query parameters, PBIDS files, and XMLA endpoints)
What-if parameters
Power Query parameters
PBIDS files
XMLA endpoints
Summary
Questions
Chapter 3: Profiling the Data
Technical requirements
Identifying data anomalies
Interrogating column properties
Examining data structures
Interrogating data statistics
Column distribution
Column profile
Summary
Questions
Chapter 4: Cleansing, Transforming, and Shaping Data
Technical requirements
Accessing Power Query in Power BI
Sorting and filtering
Managing columns
Using column transformations
Transforming any data type columns
Transforming text columns
Transforming number columns
Transforming date and time columns
Adding columns
Using row transformations
Combining data
Using merge queries
Append queries
Combine files
Enriching data with AI
Language detection
Key phrase extraction
Sentiment analysis
Image tagging
Azure ML
Using advanced operations of Power Query
Using the Advanced Editor
Using the Query Dependencies tool
R and Python scripts
Summary
Questions
Part 2 – Modeling the Data
Chapter 5: Designing a Data Model
Technical requirements
Define the tables
Flatten out a parent-child hierarchy
Star schema
Defining relationships
Cardinality
Cross-filter direction
Relationship test tips
Define role-playing dimensions
Date table as a role-playing dimension
Configure table and column properties
The General section
The Formatting section
The Advanced section
Define quick measures
Resolve many-to-many relationships
Create a common date table
Power BI date hierarchy tables
Using your own date table
Date math
Model size
Role-playing with our date table
Define the appropriate level of data granularity
Design the data model to meet performance requirements
Summary
Questions
Chapter 6: Using Data Model Advanced Features
Technical requirements
Using sensitivity labels
Implementing row-level security
Setting up row-level security
Managing row-level security
Applying natural-language Q&A capability
Using Q&A in reports and dashboards
Q&A linguistic models
Optimizing Q&A in data models
Summary
Questions
Chapter 7: Creating Measures Using DAX
Technical requirements
Building complex measures with DAX
Quick measures
Creating your own measure
Measures versus calculated columns
Default summarization
Context is everything!
Using CALCULATE to manipulate filters
Simple filtering
The FILTER function
The ALL function
Implementing time intelligence using DAX
Date tables
Role-playing dimensions
Replacing numeric calculated columns with measures
The X functions
When to use calculated columns
When to use measures
Using basic statistical functions to enhance data
Changing the default summarization
Binning and grouping histograms
Implementing top N analysis
Ranking function
Top N functions
Creating semi-additive measures
Additive measures
Non-additive measures
Semi-additive measures
Summary
Questions
Chapter 8: Optimizing Model Performance
Technical requirements
Optimizing data in the model
Removing unnecessary rows and columns
Splitting numeric and text column data
Optimizing measures, relationships, and visuals
Optimizing relationships
Optimizing visuals
Optimizing with aggregations
Query diagnostics
Session diagnostics
Step diagnostics
Understanding query diagnostics
Summary
Questions
Part 3 – Visualizing the Data
Chapter 9: Creating Reports
Technical requirements
Understanding the capabilities of Power BI
Adding visualization items to reports
Choosing an appropriate visualization type
Table and matrix visualizations
Bar and column charts
Line and area charts
Pie chart, donut chart, and treemaps
Combination charts
Card visualization
Funnel visualization
Gauge chart
Waterfall chart
Scatter chart
Map visuals
Q&A visualization
Formatting and configuring visualizations
Formatting options for a visualization
Importing a custom visual
Configuring conditional formatting
Configuring small multiples
Applying slicing and filtering
Adding an R or Python visual
Adding a smart narrative visual
Configuring the report page
Designing and configuring for accessibility
Report accessibility checklist
Configuring automatic page refresh
Creating a paginated report
Using Power BI datasets in Excel PivotTables
Summary
Questions
Chapter 10: Creating Dashboards
Technical requirements
Introducing Power BI dashboards
Creating a dashboard
Setting a dashboard theme
Using a dashboard
Pinning tiles
Optimizing dashboards
Configuring views of a dashboard
Optimizing the performance of a dashboard
Summary
Questions
Chapter 11: Enhancing Reports
Technical requirements
Using bookmarks
Using the selection pane
Creating custom tooltips
Interactions between visuals
Configuring navigation for a report
Applying sorting
Sync slicers
Using drillthrough and cross-filter
Drilling down into data using interactive visuals
Exporting report data
Designing reports for mobile devices
Summary
Questions
Part 4 – Analyzing the Data
Chapter 12: Exposing Insights from Data
Technical requirements
Exploring slicers and filters
The Analytics pane
Summary
Questions
Chapter 13: Performing Advanced Analysis
Technical requirements
Identifying outliers
Using anomaly detection
Conducting time series analysis
Grouping and binning
Grouping
Binning
Key influencers
Decomposition tree visual
Applying AI insights
Summary
Questions
Part 5 – Deploying and Maintaining Deliverables
Chapter 14: Managing Workspaces
Technical requirements
Using workspaces
Using workspace roles
Workspace licensing
Distributing reports and dashboards
Creating a Power BI app
Using deployment pipelines
Creating a deployment pipeline
Unassigning a workspace to a deployment pipeline stage
Automating deployment pipelines
Monitoring workspace usage
Using usage reports
Summary
Questions
Chapter 15: Managing Datasets
Technical requirements
Configuring a dataset scheduled refresh
Identifying when a gateway is required
Configuring row-level security group membership
Providing access to datasets
Summary
Questions
Part 6 – Practice Exams
Chapter 16: Practice Exams
Practice test 1
Practice Test 2
Answer keys
Practice Test 1
Practice Test 2
Appendix: Practice Question Answers
Chapter 1, Overview of Power BI and the PL-300 Exam
Chapter 2, Connecting to Data Sources
Chapter 3, Profiling the Data
Chapter 4, Cleansing, Transforming, and Shaping Data
Chapter 5, Designing a Data Model
Chapter 6, Using Data Model Advanced Features
Chapter 7, Creating Measures Using DAX
Chapter 8, Optimizing Model Performance
Chapter 9, Creating Reports
Chapter 10, Creating Dashboards
Chapter 11, Enhancing Reports
Chapter 12, Exposing Insights from Data
Chapter 13, Performing Advanced Analytics
Chapter 14, Managing Workspaces
Chapter 15, Managing Datasets
Index