Download Full Outline
Course
Next Level Power BI: Mastering the Power Query Editor & Creating a Robust Semantic Model
CompTIA Certified Badge
Build on your Power BI basics to create scalable data models, write advanced DAX, and optimize report performance effectively.
ID:TTDPB02
Duration:2 Days
Level:Intermediate
Format:

What You'll Learn

Overview

CompTIA Authorized Partner Badge

Take your Power BI skills to the next level with this engaging, hands-on course designed for professionals who already know the basics and want to refine their expertise. You'll gain the tools and techniques to handle real-world challenges like optimizing data quality, creating scalable queries, and building high-performing semantic models. This course focuses on practical applications, ensuring you can confidently retrieve and connect data from various sources, address data quality issues, and manipulate queries with advanced techniques. You'll also explore best practices for creating and maintaining star schemas, writing efficient DAX measures, and implementing row-level security to enhance data governance.

Through a mix of expert-led instruction and hands-on activities, you'll not only expand your technical skills but also learn strategies to make your reports more reliable, insightful, and secure. Whether you're fine-tuning relationships, improving report performance, or setting up parameters and incremental refresh, this course equips you with techniques you can immediately apply in your role. By the end of the course, you'll be ready to build Power BI reports and models that align with best practices and deliver impactful results for your organization.

Expand All

Objectives

This course combines engaging instructor-led presentations and useful demonstrations with valuable hands-on labs and engaging group activities. Throughout the course you'll learn how to:

  • Identify and retrieve data from various data sources and understand the different connection methods.
  • Connect and change connections to a variety of data sources
  • Identify problems with data quality and errors and address them
  • Leverage Applied Steps and the Advanced Editor
  • Manipulate queries to create additional queries or modify existing queries
  • Future proof queries
  • Create parameters and functions
  • Set up for incremental refresh
  • Create a star schema that meets best practice standards
  • Write DAX measures with a variety of DAX functions, including CALCULATE
  • Use variables and nested expressions to streamline DAX measures
  • Activate inactive relationships
  • Avoid many-to-many relationships
  • Set up incremental refresh in the model
  • Measure the performance of a report page
  • Use the DAX query view to view and improve the performance of DAX expressions
  • Create security roles and assign row level security through the role

Audience

This course is geared for data analysts, business intelligence professionals, and power users who are already familiar with Power BI basics but want to sharpen their skills and become more effective in their roles. Whether you're designing dashboards, creating data models, or improving reporting accuracy, this class provides the tools you need to succeed.

 

Pre-Requisites

To ensure a smooth learning experience and maximize the benefits of attending this course, you should have the following prerequisite skills:

  • Basic familiarity with Power BI tools and interface.
  • A working knowledge of importing and visualizing data.
  • General understanding of database concepts and relationships.

Power BI Essentials: Power BI for Data Transformation, Reporting and Analytics

Agenda

Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We will work with you to tune this course and level of coverage to target the skills you need most. Course agenda, topics and labs are subject to adjust during live delivery in response to student skill level, interests and participation.  

Getting the Data

  • Entering Your Credentials
  • Change Data Source Settings
  • Changing Data Source Settings
  • When a data source moves
  • Editing Permissions
  • Clear Permissions
  • Privacy Levels
  • Writing your own SQL code
  • Shared vs. local dataset
  • Using Folders and SharePoint Folders

Data Quality and Data Typing

  • Turning on the profiling tools
  • Data quality and distribution
  • Profiling on more than 1000 rows
  • Empty fields: handling blanks and nulls
  • Cardinality: what is it and why is it important?
  • Keys and IDs
  • Handling errors
  • Data Types

Applied Steps

  • The "magic" of steps: adding, deleting, re-arranging steps
  • Naming and Documenting Steps
  • Query Folding and Changed Type step
  • Using The Advanced Editor

When the existing data is not enough (or too much)

  • Replacing data
  • Adding new data
  • Adding a new column
  • Adding a new query
  • Deleting columns and rows

Manipulating Queries

  • Combining queries
  • Append, Merge
  • Duplicating a query
  • Referencing a query
  • "Helper" queries
  • Deleting a query
  • Copying parts of an M script

Future Proofing Queries

  • Resolving errors in your query
  • Read the Formula Bar
  • Re-using your work in other queries
  • Copying queries into new PBIX files

Parameters and Functions

  • What is a parameter?
  • When do you need one?
  • Why parameters are useful
  • Creating a parameter
  • What is a function?
  • When do you need one?
  • Why functions are necessary
  • Creating a function
  • What is a Semantic Model?
  • Review the fields and tables that have been imported into the Power BI Desktop.
  • Why is a data model necessary?
  • Explain the star schema
  • Discuss fact and dimension tables
  • Recognizing typical errors when the model is incorrect

Filters and Relationships

  • Explore the types of relationships: 1-to-many/many-to-1, many-to-many, one-to-one
  • Discuss the importance of filtering in Power BI
  • Creating relationships
  • Active and inactive relationships
  • Using CALCULATE to modify the model's behavior or change the filter context

Adding to the Semantic Model

  • Adding new content using DAX: tables, columns, measures
  • Hiding fields
  • Finishing touches: hierarchies, descriptions, display folders
  • Evaluate report performance and identify solutions Use different functions to accomplish the same end; evaluate performance
  • Measure individual measure performance using DAX Query view
  • In depth discussion of filter and row context in DAX

Row-level Security

  • Security overview in Power BI
  • Row-level security
  • Static Method
  • Dynamic Method

Related Courses

Power BI Essentials: Power BI for Data Transformation, Reporting and Analytics
Hands-on Power BI Boot Camp: From Data Integration to Interactive Dashboards

Connect with us

Tailor your learning experience with Trivera Tech. Whether you need a custom course offering or want to schedule a specific date and time for corporate training, we are here to help. Our team works with you to design a solution that fits your organization's unique needs; whether that is enrolling a small team or your entire department. Simply let us know how many participants you'd like to enroll and the skills you want to develop, and we will provide a detailed quote tailored to your request.

Contact Trivera Today to discuss how we can deliver personalized training that equips your team with the critical skills needed to succeed!