Microsoft Excel: Introduction to Power Pivot and Power Query Business Intelligence Tools

Microsoft Excel Training Series | Level 4

Course Details

Duration: 2 days

Microsoft® Excel® Versions: 2013 | 2016 | 365 (only specific versions of Excel include Power Pivot)

Delivery Methods: Instructor-led classroom (ILC) | Virtual classroom (VC) on request

Course Dates: July 23/24, 2018 | View schedule Course schedule

Course Fee: $595 CDN per person + HST or BYOD (bring your own device): $545 CDN person + HST*

Note: Public scheduled dates for this course are delivered as live instructor-led classroom training.

Register for this training course.

Course Overview

This course introduces students to some of the most exciting and powerful tools in Excel – Power Pivot and Power Query (Get and Transform in 2016). Power Pivot allows users to work with large datasets in Excel’s data model and manipulate them in many ways quickly and easily and then create supercharged pivot tables. During this course, students will import data into the Power Pivot data model, create relationships between tables, write calculated columns and measures and generate more powerful pivot tables from Power Pivot. Students will create expressions using DAX (Data Analysis Expressions) and update from Power Pivot data sources. In order to clean and transform data, queries will be created using Power Query or Get and Transform and then use the data within Power Pivot.

Prerequisite: Microsoft Excel: Intermediate / Advanced or equivalent knowledge and skills.

Custom training: This course may be delivered at your site or ours as an instructor-led or virtual classroom solution. Contact us at for more information including savings for custom group training.

Related training: View all Microsoft Excel courses >


  • Comprehensive course manual or published book
  • Keyboard shortcuts quick reference
  • Sample and exercise files
  • Refreshments (for classes conducted in Avantix Learning classrooms)
  • Certificate of completion
  • Follow-up email support

Course Topics

Getting Started with Power Pivot

  • Overview of the capabilities of Power Pivot
  • Versions of Excel that support Power Pivot
  • Downloading Power Pivot (2013)
  • Activating add-ins
  • Standard Excel pivot tables compared to Power Pivot pivot tables
  • Using the Data Model in Excel

Creating a Workbook with Power Pivot

  • Viewing the Power Pivot data model window
  • Examining the Ribbon in the Power Pivot data model window
  • Importing data into Power Pivot
  • Data sources supported in Power Pivot workbooks
  • Data types supported in Power Pivot workbooks
  • Returning to Excel

Creating Relationships

  • Viewing tables in Diagram View
  • Primary keys and foreign keys
  • Naming conventions for Power Pivot tables
  • Creating relationships between tables
  • Moving between views

Creating Pivot Tables and Charts with Power Pivot Data

  • Creating a pivot table from Power Pivot data
  • Creating a pivot chart from Power Pivot data
  • Adding fields from related tables in pivot tables and charts
  • Inserting slicers to use with pivot charts and pivot tables
  • Refreshing pivot tables and pivot charts

Creating Calculated Columns and Measures in Power Pivot

  • Creating calculated columns in Power Pivot
  • Creating a measure or calculated field in Power Pivot
  • Creating a measure or calculated field in Excel
  • Using Implicit vs explicit measures in pivot tables
  • Editing or renaming a calculated column or measure
  • Deleting a measures

Overview of DAX (Data Analysis Expressions)

  • Getting started using DAX
  • Where to use DAX formulas
  • Key concepts with DAX formulas
  • Creating DAX formulas
  • Types of operations you can perform with DAX
  • DAX functions compared to Excel functions
  • DAX data types
  • Key DAX functions such as CALCULATE

Refreshing or Changing Data in Power Pivot

  • Manually refreshing data
  • Automatic or scheduled refreshes
  • Recalculating formulas

KPIs (Key Performance Indicators)

  • Using KPIs with Power Pivot data
  • Setting base values, target values and thresholds
  • Editing a KPI
  • Deleting a KPI

Cleaning and Transforming Data using Power Query or Get and Transform

  • Getting and activating Power Query (2013)
  • Using Get and Transform (2016)
  • Creating a new query
  • Adding steps to a query
  • Loading a query
  • Using queried data with Power Pivot


Register Now for a public course at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada.

Do you need more information? Contact us!

To request this page in an alternate format, contact our staff.

*Prices subject to change

Copyright 2018 Avantix® Learning Inc.

Instructor writing on virtual graph in Microsoft Excel Power Pivot training course in Toronto.

You may like

How to Freeze Row and Column Headings in Microsoft Excel

As many Excel worksheets can become quite large, it can be useful to freeze row and column headings so that they are locked in place when you scroll through your worksheet. In Excel, you can freeze both row headings and column headings or just one.

10 Excel Tips for Working with the Subtotal Feature

You can insert subtotals in Microsoft Excel data sets or lists using the Subtotal feature. Check out these 10 great tips which include showing and hiding subtotals, formatting subtotals, copying only subtotals and grand totals and more …

You may also like

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 |

Upcoming Training
Microsoft Excel: Introduction to PowerPivot and Power Query Business Intelligence Tools (Toronto course)
Avantix Learning, 1 Yonge Street, Suite 1801 (Toronto Star Building),Toronto, Ontario,Canada
Next course starts
July 23, 2018
Next course ends
July 24, 2018

Pin It on Pinterest