Power Pivot and Power Query for Excel 2016

Learn how to use the Excel 2016 add-ins Power Pivot and Power Query to manipulate, query, distribute and visualize your data.

Power Pivot, Power Query, Power View, 3D maps are free add-ins that expand and combine the features and capabilities of Excel. It includes the ability to import, manipulate and work with large amounts of data from a variety of data sources both inside and outside Excel. 

This course will cover techniques for manipulating and querying data, the structure of DAX functions, ways to distribute and visualize data using Pivot Charts, Slicers, Sparklines, Timelines, Power View and 3D maps.

By the end of this course, users should be comfortable with navigating the Power Pivot and Power Query applications, manipulating data, creating queries and reports, using DAX functions, and distributing and visualizing data.


Students should be comfortable using the Excel 2016 interface and have a firm understanding of how Excel works and what it is used for. Understanding and experience with formulas, functions, and PivotTables is strongly recommended.

Getting Started with Power Pivot

Enable and Navigate

Import Data from Various Data Sources

Refresh Data from a Data Source

Create Linked Tables

Manipulating Power Pivot Data

Organize and Format Tables

Create Calculated Columns

Sort and Filter Power Pivot Data

Create and Manage Table Relationships

Creating Power Pivot Reports

Manipulate Power Pivot Data Using DAX Functions


DAX Functions

DAX Variables

Aggregate Functions

How to Manipulate Power Pivot Data Using DAX Functions

Extract Data from Tables Using Functions

Filter Functions

How to Extract Data From Tables Using Functions

Work with Time Dependent Data

Time Intelligence Functions

Create a Date Table

How to Work with Time Dependent Data

Distributing Power Pivot Data

Data Sources

Connect to a web data source

Connect to a spreadsheet

Connect to a database

Organizing Data

Build repeatable processes to filter, clean, aggregate, and transform your data.

Shape data in the subject table

Remove Columns

Replace values

Filter values in a column


Name a query

Load the query to a worksheet

Create PivotTables

Flattened PivotTables

The Power Pivot Field List Pane

Creating Key Performance Indicators

Bringing It All Together with Visualizations

Create PivotCharts

Create Power Views

Create 3D Maps

Filter Data Using Slicers

Create Sparklines

Create Timelines

Conditional Formatting

Creating a dashboard

Program Details
Duration 2 Days
Capacity Max 12 Persons
Training Type Classroom / Virtual Classroom

Can't find what you're looking for? Let us know if you have a query or cannot find what you are looking for.