Certificate Authentication

Day 1

Overview of Power BI

• The Value Offering

• Licensing and Pricing

• Market Positioning

Power BI Cloud Service (the wow features)

• Overview of the Cloud Service

• Role and Functionality for Consumers

• Building a CFO Dashboard

• Dashboard Flow and Navigation

• Power BI for Mobile

• How to use Q&A (Natural Language

Queries)

• Obtaining Value from Quick Insights

• Connecting to Services (Content Packs)

• Using the Demo Samples

• Publish to web (Embedded View)

• Exporting Reports to PowerPoint

Overview of Power BI Desktop

• Similarities to Excel Power Pivot

• Similarities to Excel Power Query

• Technical Options & Settings

• Monthly Updates

• Overview of connecting to various data sources

• Data Modeling 101

• The 'Ideal' Data Structure

• Introduction to the concepts of Facts & Dimensions

Importing and Transforming Data

• Importing Data

• Overview of Data Source Options

• Query Building Best Practices

• Query Editor Overview

• Understanding Query Design & the ETL Process

• Understanding Applied Steps

• Data Transformations

o Cleaning data (covers many different methods)

o Filtering Queries

o Reducing Columns

o Reducing Rows

o Merging Datasets

o Unpivoting Data

o Combining Files from a Folder

o Appending Datasets

o Creating Columns from Examples

o Creating Conditional Columns

o Replacing Values

o Formating Data

• Understanding the Value of a DateDim Table

• Adding a Dynamic Calendar Table

• Creating & Using Parameters for the Calendar

• Changing Data Naming Conventions

• Grouping Queries

• Introduction to the M Language

• Using the Power Query Formula bar

• Understanding and Changing Data Types

• Handling Different Data Formats

• Column Profiling & Distribution

• The Advanced Editor

• Query Dependencies

• Checking For Errors & Error Handdling

Overview of Power BI Desktop

• Similarities to Excel Power Pivot

• Similarities to Excel Power Query

• Technical Options & Settings

• Monthly Updates

• Overview of connecting to various data sources

• Data Modeling 101

• The 'Ideal' Data Structure

• Introduction to the concepts of Facts & Dimensions

Day 2

Data Modelling

• Creating and Understanding Relationships

• Handling Multiple Relationships with the Fact Table

• Default Summarization of Fields

• Custom Sorting of Columns

• Formatting Columns

• Optimizing Q&A - Adding Data Synonyms

• Adding New Model Views

• Creating Model Folders

• Default Settings for Relationships

• Using the Power BI Desktop Data View

• Sorting, Filtering and Validating Data

• Data Modelling Best Practices

• Using additional model layouts

Creating a STAR Schema

• Normalizing a model

• Working with flat files

• Working with Snowflakes or overly Normalized Models

• Working with Multiple Fact tables

• Creating Common Dimensions Tables

Introduction to Measures

• Introduction to DAX

• Understanding DAX Syntax

• DAX Conventions

• Implicit and Explicit Measures

• Understanding Base Measures & Metrics

• Difference Between Calculated Columns and Measures

• Understanding Calculated Tables

• When to create Calculated Columns instead of Measures

Common DAX Functions

• Build a Time Example Intelligence Report

• Default Visuals in Power BI

• Changing the visual sort by field

• Commonly Used DAX Functions

o Sum, Average, Count, DistinctCount

o Time Intelligence (MTD, QTD, YTD, Prior Year)

o Using a Safe Divide Function

• Time Intelligence with Fiscal Years

• Adding a slicer to a page

• Supplementing Visuals with Cards

• Optimizing Measures for Self-Service BI

• Using Quick Measures

Adding Last Refresh or Last Transaction Dates

• Creating Dynamic Report Headings & Dates

• Viewing the Underlying Data

• Customising Report Visual Interactivity

• Exploring the Underlying Data Driving Visuals

• Exporting Data from Power BI

• Formating Tips & Tricks

• Removing Clutter in the Report

• DAX best practices and Guidelines

• Adding Comments to DAX Expressions

• Duplicating & Renaming Pages

• Using SUMX

• Creating % measures

• Showing Variances and %

Understanding Filter Concepts

• Using the R.H.S Filters Pane

• Optimizing Slicers and using Hierarchy Slicers

• Filter Context through Cross Filtering or Highlighting

• Modifying or Ignoring the Filter Context

• Understanding the Visual Filter Context Cards

• Syncing Slicers

• Applying Report Level Filters

• Understanding the Filter and Row Context

• How the Filter Context Affects Table Total

Day 3

Report Building

• Build a Monthly Trend Report

• Using 'Combo Charts'

• Combining Measures in a Visual

• Making Report Enhancements

• Layout Concepts

• Alignment and Sizing Tips

• Modifying the default Theme

• Making Reports more Compelling

Report Functionality

• Adding Drill-Downs

• Enhancing Tooltips

• Explaining the Increase or Decrease

• Analyzing the Distribution

• Moving away from 1 Dimensional Representations

Report Visualisations

Choosing Visuals Based on Context

• Formatting Visuals

• Using Constant lines

• Inserting, Min, Max and Average Lines

• Understanding KPI cards

• Optimizing Visual, Page & Report Level Filters

• Using Relative and TopN filters

• Building Tabular Reports

• Replicating Excel Pivot Tables

• Types of Conditional Formatting

• Optimizing the Matrix Layouts

• Drilling on Columns and Rows

• Using the Stepped Layout

• Optimizing Waterfall Charts

• Exploring Map Visualizations

• Creating Year Over Year Trends

• Using Custom Visuals

• Top 10 & Bottom 10 Reports

Optimising the Design

• Adding Drill Through Pages

• Optimizing Labels and Titles

• Using the Format Painter

• Using Background Images as Templates

• Custom Page Sizing

• Using Logos & Custom Icons

• Using the Themes Gallery in Power BI

• Making Enhancements with Shapes and Lines

• Best Practice Guidelines and Design Tips

• Removing Default Setting Redundancies

• How to Create Historical Categorical Trends

• Using Inactive Relationships

• Build a Qty Sold Vs Qty Shipped Report

• USERELATIONSHIP Function

• Conditional Formating of Titles and Backgrounds

• Creating Dynamic Page Headings and Titles

PowerBI Bookmarking

• Understanding the Value Offering

• Using Bookmarks for Presentations

• How to use Bookmarks to Enhance the Visual Story

• Switching Between Chart Types

• Use Buttons or Images with Bookmarks

• Creating a Report Menu Page with Bookmarks

• Consolidating Report Views onto a Single Page

• Other Use Cases

Letting Users Personalise Visuals

• What Report Users can change

• Use Perspectives for a more focused view

• Enable Personalization in a Report

• Turn the Feature on or off at a Page or Visual Level

Day 4

Distribution

• Publishing Models to the Cloud Service

• Sharing Content from 'My Workspace'

• Creating App Workspaces

• Collaborating on Reports

• Version Control

• Workspace Permissions

• Publishing Organisational Apps for Distribution

• Connecting to Apps

• Updating Apps

• Commenting in Workspaces and Apps

• Creating Alerts

• Subscribing to Dashboards

• Creating Featured Q&A Questions

Governance and Administration

• Understanding Row Level Security

• Creating and Testing Security Roles

• Adding Users to Security Roles

• Role of a Power BI Cloud Service Administrator

• The Admin Portal

• Creating Office 365 Security Groups

Dynamic Row Level Security (RLS)

• Usage Scenarios

• Mapping User IDs to the Data Model

• DAX Functions for Returning Usernames

• Adding Roles in the Power BI Desktop

• Assign Users to Power BI Security

Data Refreshing

• Overview of the Data Gateway

• Scheduling Data Refreshes

• Refreshing On-Demand

Using a Common Data Model

• Understanding the value offering

• Connecting to Power BI Data Sets (Live Connect)

• What is the CDM (Common Data Model)

Overview of Dataflows

• Understanding the Value Offering

• How to use Dataflows

• Self-service Data Prep in Power BI

Using What-if Analysis

• Understanding the What-if Parameter Concepts

• Build a What-if Analysis Report

• The Building Blocks of the Solution Architecture

• DAX: 'GENERATESERIES' & 'SELECTEDVALUE'

• Showcasing Other Use Cases

Slicing Reports by Measures

• How to Switch between Comparison Metrics

• Creating Helper tables in Power BI

• How to Capture Slicer Selections with DAX

• Using the DAX 'SWITCH' Function

• Creating Dynamic Headers to Show Changing Measures

• Showcasing Other Use Cases

Day 5

Advanced Tabular Layouts

• Overcoming Layout Limitations with Custom Mapping

• Creating a Dynamic Mapping Table

• Adding conditional columns in Power Query

• Adding Mapping Tables to the Data Model

• Custom Sorting of Reporting Rows

• Creating a Basic Profit and Loss Report

• How to apply the Concepts to Personal Finance Data

Filter Contexts with DAX

• Understanding the DAX Filter Context

• Modifying the filter Context

• Ignoring the filter context

• Filtering with 'Calculate'

• Using the 'FILTER' Function

• Using Relative and TopN Filters

Tooltip Report Pages

• Create a Tooltip page

• Configuring Tooltip Pages

• Tooltip Fields

• Mannualy setting a Report Tooltip

• Report tooltips and cross-highlighting

Exploring External Tools

• Overview of DAX Studio

• Overview of Tabular Editor

• Using the built-in Power BI Performance Analyzer

Using Query Parameters and PBI Templates

• Creating Parameters in Power BI

• Passing Parameters into Queries

• Creating Power BI Template Files

• Using Template files with Query Parameters

• Changing Parameters from the cloud service

Exploring AI Visualisations

• Understanding the Key Influences Visual

• Structuring and Optimizing AI visuals

• Working with the Decomposition Tree Visual

• Optimizing the results with filters

• Using the Smart Narrative Visual

• Adding Custom Measures into Text Fields

• Using the Q&A Report Visual

• Consolidating Results with other Visuals on the Page

• Forecasting in Power BI

Exploring other data sources

• Connecting to SharePoint Folders

• Connecting to APIs or Websites

• Connecting to SQL Server

• Using Direct Query Mode

Review of Best Practices

• Summarising key concepts

• Review of additional Power BI features (time permitting)