Course details

Duration: 2-days

Location: This course is run virtually online.

This course goes beyond the basics and includes a comprehensive yet balanced look at the four main components that make up Power BI Desktop: Report view, Data view, Relationships view, and the Power Query Editor. It also demonstrates the use of the online Power BI Service.

It looks at authoring tools that enables you to connect to and transform data from a variety of sources. This allows you to then produce detailed reports through a comprehensive set of visualisations. Once you have those reports, it’s a seamless process to then share them with your colleagues, inside or outside of your organisation, by publishing to the Power BI Service.

The aim of this course is to provide an understanding of the Power BI analysis process, by working with real-world examples that will equip you with the necessary skills to start applying your knowledge straight away.

Prerequisite

An introduction to intermediate knowledge of Microsoft Excel is preferable.

Learning objectives

Getting Started

  • The Power BI process
  • Launching Power BI Desktop
  • The four views of Power BI
  • The report canvas

Getting Data

  • Connect to data sources
  • Connect to an Excel file
  • Connect to a CSV file
  • Connect to a database
  • Import vs. DirectQuery
  • Connect to a web source
  • Connect to a folder of files
  • Create a data table

Transform Data in the Query Editor

  • Clean data in the Query Editor
  • Remove rows by filter
  • Replace values
  • Field data types
  • Add a custom column
  • Append data to a query
  • Add an index column
  • Fix error issues
  • Basic maths operations

Build a Data Model

  • Data model relationships
  • Manage table relationships

Merge Queries

  • Table join kinds
  • Merge tables

Create Report Visualisations

  • Create and format visuals
  • Create map visuals
  • Format map visuals
  • Create chart visuals
  • Format chart visuals
  • Table, matrix, and card visuals
  • Format table visuals
  • Filter reports with slicers
  • The marketplace visualisations
  • Export report data to Excel

The Power Query Editor

  • Fill data up and down
  • Split columns by delimiter
  • Add a conditional column
  • Add a custom column
  • Merge columns

M Functions in the Query Editor

  • Write an M function
  • Write an IF function
  • Nest AND functions in an IF function

Pivot and Unpivot Tables

  • Pivot tables in the query editor
  • Pivot and append tables
  • Pivot but don’t summarise
  • Unpivot tables
  • Append mismatched headers

Group Data in Power Query

  • Group rows in a table

Introduction to Calculated Columns

  • Power BI measures
  • Add your first calculated column
  • The SWITCH function

Introduction to DAX Measures

  • The COUNTROWS measure
  • The DISINCTCOUNT measure
  • DAX rules
  • The CALCULATE measure
  • Things of note about CALCULATE
  • The SUMX measure
  • Anatomy of SUMX
  • When to use “X” measures

Introduction to Time Intelligence

  • Standard vs. custom calendar
  • Calendar: a special lookup table
  • Mark a calendar as a date table
  • Time intelligence functions
  • The TOTALYTD measure
  • Change year-end in TOTALYTD

Hierarchy, Groups and Formatting

  • Create a hierarchy to drill data
  • Compare data by group
  • Use bins to group numbers
  • Add conditional formatting

Publish and Share Reports Online

  • Publish to the BI online service
  • Upload reports from BI service
  • Get quick insights
  • What is Q&A?
  • Sharing your reports

The Power BI Mobile App

  • Get Power BI for mobile
  • Using Power BI mobile

Apply Your Learning

  • Take away lesson