Power Pivot and Power Query for Excel 2016

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

• Measures
• 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

Power Query and When to Use It

(NOTE: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both.)

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

Queries

• 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

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

• Measures
• 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

Power Query and When to Use It

(NOTE: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both.)

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

Queries

• 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