Course details

Duration: One-day

Location: This course is run remotely online.

This course is designed for those already using Power BI Desktop and are ready to get to grips with the creation and management of Power BI elements. At the same time maintaining a balanced look at the Query Editor to transform data, with a deeper dive into calculations and measures to enhance dashboard creation.

The aim of this course is to go beyond the basics and explore the analysis capabilities in more detail, working with examples that will take you to the next level in your use of Power BI.

Prerequisites

My assumption is that you are a Power BI user and already know some things before attending this course. For example, you know how to connect to a data source, and once you have, you know how to add and remove columns, and change data types for example. If not, I recommend attending the Power BI Introduction course first.

Learning objectives

Lesson 1. Connecting to Files

  • Connecting to databases
  • Import vs. Direct Query
  • Connect to a folder of files
  • Managing file connections

Lesson 2. Transforming Tables

  • Add a custom column
  • Appending tables together
  • Add an index column
  • Fixing error issues
  • Perform math operations

Lesson 3. The Data Model

  • Table relationships
  • Managing relationships

Lesson 4. Merging Queries

  • Table join kinds
  • Merging tables

Lesson 5. The M Functions

  • The M language
  • Add a conditional column
  • M formulas in custom columns
  • Merging columns
  • Creating M functions
  • Query group folders
  • Create an IF function
  • Using AND in an IF function

Lesson 6. Pivoting Tables

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

Lesson 7. Group Columns

  • Grouping table columns

Lesson 8. The Data Model Revisited

  • Data model relationships
  • Mark a calendar as date table

Lesson 9. Types of DAX Formulas

  • Calculated columns and measures
  • Creating a new calculated column
  • The SWITCH function
  • Using TRUE in a SWITCH function

Lesson 10. Writing DAX Measures

  • Creating DAX measures
  • Controlling field summarisation
  • Data classification
  • COUNTROWS and DISTINCTCOUNT functions
  • DAX rules

Lesson 11. The CALCULATE Measure

  • Filtering with CALCULATE
  • Things of note about CALCULATE

Lesson 12. The SUMX Measure

  • The X iterator functions
  • Anatomy of SUMX
  • Create a SUMX
  • When to use X-functions

Lesson 13. Time Intelligence Defined

  • Importance of a calendar table
  • The TOTALYTD measure
  • Changing the fiscal year-end date
  • Use DATEADD to compare historical

Lesson 14. Enhanced Report Visuals

  • Creating and formatting charts
  • Utilising report themes
  • Control a field’s number format
  • Filtering across report pages
  • Import online visuals
  • Create hierarchies to drill layers
  • Compare data in groups
  • Adding visual conditional formatting

Lesson 15. Publish and Share Reports

  • Get quick insights
  • What is Q&A?
  • Pinning visual to a dashboard
  • Sharing dashboards
  • Export dashboards