Course details

Duration: 2-days

Location: This course is run virtually online or face-to-face onsite.

This course starts with the basics then moves seamlessly to an intermediate level. It includes a comprehensive yet balanced look at the four main components that make up Power BI Desktop: Report view, Data view, Model view, and the Power Query Editor. It also demonstrates how to use the online Power BI service.

It looks at authoring tools that enable you to connect to and transform data from a variety of sources, allowing you to produce detailed reports through a range of visualisations in dynamic, interactive dashboards. Once you have those reports, it looks at the seamless process of sharing those with colleagues, inside or outside your organisation, by publishing to Power BI service.

The aim of this course is to provide a more complete 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 intermediate understanding of Microsoft Excel is preferable.

Learning objectives

Lesson 1. Getting Started

  • Opening Power BI Desktop
  • The Power BI ecosystem
  • The four views of Power BI
  • Visualisations at a glance

Lesson 2. Importing Files

  • The process of importing files
  • Importing an Excel file
  • Importing a CSV file
  • Importing a database
  • Connect to an SQL server database
  • Import vs. Direct Query
  • Importing web data
  • Importing a folder of files
  • Managing file connections

Lesson 3. Shape Data in the Query Editor

  • The process of shaping data
  • Working with applied steps
  • Managing data types
  • Removing unwanted rows
  • Importing multiple tables
  • Add a custom column
  • Appending tables together
  • Fixing error issues
  • Basic math operations

Lesson 4. The Data Model

  • Table relationships
  • Relationship properties

Lesson 5. Merging Queries

  • Table join kinds
  • Merging tables

Lesson 6. Creating Report Visuals

  • Things to keep in mind
  • Inserting maps
  • Formatting maps
  • Inserting charts
  • Formatting charts
  • Controlling number formats
  • Inserting a table and matrix
  • Inserting cards for KPIs
  • Utilizing themes
  • Adding data bars
  • Highlighting key points
  • Filtering reports with slicers
  • Sync slicers across pages
  • Custom web visuals

Lesson 7. Publish and Share Reports

  • Publishing to Power BI service
  • Editing online reports
  • Pinning visuals to the dashboard
  • What is Q&A?
  • Sharing dashboards
  • Get quick insights
  • Exporting reports to PowerPoint
  • Exporting reports as PDF files

Lesson 8. The Power Query Editor

  • Fill data up and down
  • Split column by delimiter
  • Add a conditional column
  • Writing formulas
  • Merging columns

Lesson 9. The M Functions

  • Writing text functions
  • Write an IF function
  • Create a query group

Lesson 10. Pivoting Tables

  • Pivot a table
  • Pivot and append tables
  • Pivot but don’t summarise
  • Unpivot tables
  • Append mismatched headers

Lesson 11. Data Modelling Expanded

  • Creating relationships
  • Mark a calendar as a date table

Lesson 12. Two types of DAX Formulas

  • New columns vs. measures
  • Adding new column calculations
  • The SWITCH function

Lesson 13. Introduction to DAX Measures

  • Common measure functions
  • Insert a SUM
  • Insert a COUNTROWS
  • Insert a DISINCTCOUNT
  • Insert a DIVIDE
  • DAX rules

Lesson 14. The CALCULATE Measure

  • The Syntax of CALCULATE
  • Insert a CALCULATE
  • Things of note

Lesson 15. The SUMX Measure

  • X iterator functions
  • Anatomy of SUMX
  • Insert a SUMX
  • When to use SUMX

Lesson 16. Time Intelligence Measures

  • Importance of a calendar table
  • A special lookup table
  • Insert a TOTALYTD
  • Change financial year end date

Lesson 17. Hierarchies and Groups

  • Create a hierarchy to drill data
  • Compare data in groups