Book a course

1 day instructor-led or online virtual course

What you will learn

This balanced one day course starts by showing you how to import and connect to data from a variety of different sources, including Excel, CSV files and the web.

It then focuses on the process of transforming and cleaning data, and appending and merging data tables together. You will learn how to run queries across tables by implementing table relationships know as Data Models.

Then we look at outputting the resulting data sets in a range of visualisations to create detailed reports. The course finishes by looking at the process of sharing those reports on the web.

Prerequisite

An intermediate knowledge of Microsoft Excel and access to Office 365 is preferable.

Course outline

Getting Started

  • The Power BI process
  • Launch 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 web source

Transform Data in the Query Editor

  • Clean data in the Query Editor
  • Remove rows by filter
  • Replace values
  • Field data types
  • Fill data up and down
  • Split column by delimiter
  • Add a conditional column
  • Add a custom column
  • Pivot table data
  • Pivot and append data
  • Append data to a query
  • Add an index column

Build a Data Model

  • Relate tables in a Data Model
  • Manage table relationships

Merge Queries

  • Table join kinds
  • The merge process

Create Report Visualisations

  • Create and format visuals
  • Create map visuals
  • Format map visuals
  • Create chart visuals
  • Format chart visuals
  • Table, matrix and card visuals
  • Control number formats
  • Filter reports with slicers
  • Page and report level filters
  • Visuals from the marketplace
  • Export report data to Excel

Introduction to New Column Formulas

  • Add your first new column
  • The SWITCH function

Introduction to DAX Measures

  • The COUNTROWS function
  • The DISTINCTCOUNT function
  • DAX rules
  • The CALCULATE function
  • Things of note about CALCULATE

Introduction to Time Intelligence

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

Hierarchy and Conditional Format

  • Create a hierarchy to drill data
  • Add conditional formatting

Publish and Share Your Report

  • Publish to BI online service
  • Get quick insights
  • What is Q&A?
  • Share your report

More about Power BI