Course details

Duration: 1-day

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

This course is designed for those already using Power BI Desktop and are ready to work with further analytical elements of Power BI reports. The course maintains a balanced look at the different areas data analysis including the Query Editor to transform data, a deeper dive into writing DAX measures, data modelling, and enhanced dashboard visualisations.

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

Prerequisites

My assumption is you are already using Power BI and are ready to move beyond the basics. If not, I recommend attending the Power BI Introduction course first.

Learning objectives

Lesson 1. Importing Files

  • Importing a database
  • Importing an SQL Server Database
  • Import vs. Direct Query
  • Import a folder of files

Lesson 2. Shape Data in the Query Editor

  • Add a custom column
  • Appending tables together
  • Prevent tables loading in reports
  • Fixing error issues
  • Perform math calculations

Lesson 3. Merge Queries

  • Table join kinds
  • Merging tables

Lesson 4. Conditional and Custom Columns

  • The M language
  • Add a conditional column
  • Custom column formulas
  • Merging columns
  • Creating text functions
  • Creating IF functions
  • Query group folders

Lesson 5. Pivoting Tables

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

Lesson 6. Data Modelling

  • Data model relationships
  • Mark a calendar as a table

Lesson 7. DAX New Columns

  • Creating DAX columns
  • The SWITCH function
  • Including TRUE within SWITCH

Lesson 8. Writing DAX Measures

  • The COUNTROWS measure
  • The DISTINCTCOUNT measure
  • The DIVIDE measure
  • DAX rules

Lesson 9. The CALCULATE Measure

  • Filtering with CALCULATE
  • Controlling field summarisation
  • CALCULATE with multiple filters
  • Things of note about CALCULATE

Lesson 10. The SUMX Measure

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

Lesson 11. Time Intelligence Measures

  • Importance of a calendar table
  • The TOTALYTD measure
  • Changing financial year end
  • Viewing date calculations in charts
  • Compare historical data with DATEADD

Lesson 12. Dashboard Visualisations

  • Utilising report themes
  • Control visualisation formats
  • Filtering across report pages
  • Data mining with hierarchies
  • Compare data in groups
  • Adding conditional formatting

Lesson 13. Power BI Service

  • Importing reports in service
  • Get quick insights
  • Sharing dashboards
  • What is Q&A?
  • Sharing dashboards in SharePoint online