Audience
This advanced 2-day course is intended for anyone who has existing experience working with Power BI and who wants to extend and enhance their knowledge beyond the basics.
Prerequisites
Delegates should have previously attended the 2-day Microsoft Power BI Introduction course, or possess equivalent knowledge and experience of Power BI.
Duration
2 days. Hands on.
Course Objectives
This 4-day course is intended for anyone new to designing and implementing self-service business
intelligence solutions with Microsoft Power BI Desktop and the Power BI Service. The course structure
is very comprehensive and provides a total immersion in the subject matter. The training is delivered
with plenty of illustrated examples and augmented with practical hands-on exercises to enhance the
learning experience. Also, as Power BI is continually evolving, this course is regularly reviewed and
updated to keep the content as fresh and relevant as possible, thus ensuring maximum productivity
from the learning experience. Due to the very comprehensive nature of this course, the content
delivered may vary depending on new and updated features, and client requirements.
The course structure takes a broad and practical approach to Power BI, defining best practices and
demonstrating useful techniques that will prove extremely useful in the workplace. The content
delivered will also help delegates prepare for the “PL-300 Microsoft Power BI Data Analyst” exam.
The first part of the course introduces the capabilities and components of Power BI before delving into
the realms of discovering and loading data from a variety of sources. Primarily focussed on using
Power Query to create robust queries, students will learn about issues surrounding data quality, how to
cleanse data, how to transform and then restructure data, and how to mashup data from various sources
by merging and appending data. We will also examine how to manage, structure, and parameterise
queries. Although a lot can be achieved working with the user interface in Power Query, we will also
delve behind the scenes and introduce the M language, highlighting ways in which existing code can be
tweaked and how user defined code and functions can be implemented.
Part two of the course introduces students to tabular data modelling concepts, preparing the data in
readiness for use in reports and dashboards. The importance of implementing relationships is also
covered. In order to enhance the data model, students will also learn about the DAX expression
language to enable them to implement calculated columns, measures, and calculated tables.
Consideration is also given to implementing time intelligence in the design of data models.
In part three, you will learn how to create and design reports and implement visuals. We will first look
at text-based visuals before moving on to graphical components like charts, gauges and KPIs (Key
Performance Indicators). The implementation of maps in reports is also given coverage. To create
interactivity in reports, students will also learn how to implement various types of filters and slicers.
Finally, in part four, we initially look at how we can further enhance the user experience by
implementing features such as drillthrough reports, bookmarks, and showing and hiding content. Then,
in order to share reports created in Power BI Desktop, we turn our attention to the Power BI Web
Service. Attendees will learn how to publish reports to the Power BI Service and how to organize and
secure content. The creation and use of dashboards will be considered, and different ways of sharing
content will also be given coverage. We will look at user accessibility issues and consider how users,
including mobile users, can interact with report content. Managing and refreshing datasets in the Power
BI Service is also included.
Additionally, with the course structure being fully modularised, customised versions of this course can
be devised and delivered to suit individual requirements – just ask for details.
Upon successful completion of this course, students will be able to:
- Understand the Role Power BI plays in
Self-Service BI
Understand the Constituent Parts of Power
BI Desktop
Discover and Load Various Types of Data
Improve and Enhance Data Quality
Transform Data
Restructure Datasets
Combine Data from Multiple Sources
Parameterise Queries
Implement a Tabular Data Model
Create and Manage Relationships
Understand the DAX Expression
Language
Implement Calculated Columns
Implement and Work with Measures
Implement Time Intelligence
Create Reports with Text-Based Visuals
Implement Conditional Formatting
Create and Implement Charts
Implement Gauges and KPIs
Implement Maps
Implement Slicers and Filters
Implement Cross Visual Interactions
Analyse Data with Advanced Visuals
Enhance the Report Consumer Usability
Experience
Understand the Role of the Power BI
Service
Publish Reports
Create and Manage Dashboards
Manage and Secure Content in the Power
BI Service
Create and Distribute Apps
Course Outline
Part 1: Introduction and Power Quarry
1. Introducing Power BI
Describe the Power BI Products and Services
Introducing Power BI Desktop
Power BI Licensing
2. Loading Data
Load Data from Text Files
Load Data from Excel Workbooks
Load Data from Microsoft SQL Server
Comparing Import and DirectQuery Storage Modes
Load Data from OneDrive and SharePoint
Load Data from the Internet
3. Transforming Data
Profile Data
Cleanse Data
Transform Data
Query Folding
Shape Datasets
Merge and Append Data
Fuzzy Lookups
Structure and Manage Queries
View Query Dependencies
Power Query Parameters
Power BI Template Files
Introduction to the M Language
Create a User Defined Function with M
Part 2: Data Modelling and DAX
4. Data Modelling
Basic Data Modelling Concepts
Fact and Dimension Tables
Configure Table and Column Properties
Categorise Data
Sorting Columns
Create Hierarchies
Group and Bin Data
Organise Content with Display Folders
Hiding Columns and Tables in Report View
Create and Manage Relationships
Understand Filter Propagation
5. The DAX Expression Language
Introduction to DAX
Extend the Data Model with Calculated Columns
Create Custom Groups
Add Measures to the Data Model
Implicit v Explicit Measures
Manipulate the Filter Context with CALCULATE
Use Variables
The DAX Query View Environment
Iterator Functions
Using the FILTER Function
Create Calculated Tables
Create a Date Table
Analyse Data over Time (Time Intelligence)
Suppress the Display of Totals in a Visual
Display Selected Filter Values in a Visual
Dynamic Format Strings
Part 3: Reports and Visuals
6. Visualising Data
Implement and Format Table and Matrix Visuals
Rename Fields in Visuals
Show Items with no Data
Conditionally Format Visual Content
Use Card Visuals
Implement and Format Charts
Report Themes
Use the Small Multiples Feature
Sparklines
Use Tooltips
Implement Reports as Tooltips
Continuous v Categorical Axis
Automatically Find Clusters of Data
Animate Scatter Charts
Use the Analytics Pane
Forecasting & Anomaly Detection
Use the Analyse Feature
Tree Maps
Analyse Data with a Decomposition Tree
Gauges and KPIs
Implement Maps
Resolve Location Errors in Maps
Implement Slicers
Visual, Page, and Report Level Filters
Synchronise Slicers across Pages
Cross-Filtering and Cross-Highlighting
Import Visuals from Microsoft AppSource
Part 4: Enhancing and Publishing Reports
7. Enhancing the User Experience
Drillthrough Reports
What-If Parameters
Field Parameters
Implement Bookmarks
Implement Buttons
Text Boxes, Shapes, and Images
Page & Bookmark Navigation
Show and Hide Content
Implement and Manage Q&A
Smart Narrative
Mobile Report Layout
Useful External Tools and Resources
8. The Power BI Web Service
Introduction to the Power BI Web Service
Publish Reports to the Power BI Web Service
Manage and Organise Content in Workspaces
Workspace Folders
Create and Manage Dashboards
Dashboard Themes
Set up Data Alerts
Share Content and Workspaces
Create and Distribute Apps
Secure Data with Row Level Security (RLS)
Refresh Datasets
Understand Data Gateways
Export Reports