Duration 2 Days

Course Content

  • What is Power Pivot ?
  • What are the benefits of using Power Pivot ?
  • How to enable the Power Pivot add-in
  • The Power Pivot Window

Importing Your Data and the Data Model

  • How to import data into the PowerPivot Data Model.
  • Importing Data from a variety of Data Sources
  • Working with Data Connections
  • Refreshing Data

Creating the Power Pivot Data Model

  • How to create relationships between tables in the PowerPivot Data Model.
  • Understanding One-to Many Relationships

Excel Analysis Tables

  • What is an Excel Analysis Table?
  • Using Slicers to filter data in the Table
  • Why Excel Tables are important to Power Pivot
  • Adding Linked Tables to the Data Model

Analysing Data using a Pivot Table

  • Understanding Compact Layout
  • Changing the Layout
  • Using the in-situ filters
  • Grouping data for ad-hoc subtotals
  • Grouping date columns into years, months

Creating PivotTables with PowerPivot

  • How to create a PivotTable with PowerPivot data
  • How to use the Field list with the Data Model
  • What is different in a PowerPivot Pivot Table
  • How Drill-down differs in PowerPivot.
  • Creating a Flattened Pivot Table to analyse combinations of unique data.

Working with Dates in Power Pivot

  • How to analyse by Year, Quarter, Month etc.
  • Why a Date Table (Calendar Table) is required
  • Generating & Marking as a Date Table.

Using Slicers to Filter Data

  • How to filer data using Slicers
  • Connecting Slicers to multiple Tables

Using Pivot Charts and Dashboards

  • What is different about PowerPivot Charts
  • Connecting Slicers to Pivot Charts
  • Creating “Dashboards”.

Creating Simple Calculated Columns

  • How to create a Calculated Column
  • Looking up values from related tables
  • What is a Calculated Field
  • How to Add a Calculated Field to a PowerPivot PivotTable
  • What are PowerPivot Sets?

Power Query

  • Adding Data to Power Query from various data sources
  • Creating a Query, the Query Editor Window
  • Load to Worksheet and Load to Data Model
  • Remove Duplicate Data in Columns
  • Split and Merge Columns of Data
  • Group data to create summary Queries
  • Merge and Append Queries
  • Transpose Data to swap Rows and Columns
  • Filling data Down populating missing values
  • Unpivot Columns: flatten a cross tab table
  • Duplicate Columns
  • Creating Custom Columns and Additional Date Columns
  • Working with Applied Steps
  • Importing Data from a Folder

Power View

  • The Power View Field List
  • Using Text Boxes
  • Creating Tables
  • Changing Table Visualisations
  • Using a Matrix and Enable Drill Down
  • Using Cards; Setting up Titles and Pictures
  • Creating Chart Visualisations and Interactive Charts
  • Creating Power View Slicers
  • Using Tiles
  • Using Power View Filters
  • Working with interactive Bubble Charts using the Play Axis
  • Creating Power View Maps

Power Map

  • Create a Power Map & Plot Geographic Data
  • Creation Visualisation; Column, Bubble, Heatmap, Regions
  • Using the 3-D Environment
  • Using Charts with Power Maps
  • Working with Layers and adding Scenes
  • Adding Annotations
  • Visualising Data over Time
  • Playing the Tour