Microsoft Power BI Data Analyst Certification Guide

Microsoft Power BI Data Analyst Certification Guide

by Orrin Edenfield, Edward Corcoran
Released June 2022
Publisher(s): Packt Publishing
ISBN: 9781803238562

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

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

Product information

  • Title: Microsoft Power BI Data Analyst Certification Guide
  • Author(s): Orrin Edenfield, Edward Corcoran
  • Release date: June 2022
  • Publisher(s): Packt Publishing
  • ISBN: 9781803238562