Microsoft Excel Power Pivot

21/01/2017

Microsoft Excel Power Pivot Training Course Outline

Duration: 1 Day

Course Objectives

After completing this course you will be able to describe the main ways to import and query data using Excel Power Pivots workbooks in Excel, to generate PivotTable and PivotChart reports and to create relationships between Power Pivot tables.

Target Audience

Those who want to use Excel Power Pivot full capabilities for Business Intelligence reporting. Previous knowledge of standard Excel PivotTable features and a basic understanding of relational data sets and data normalization would be useful but not essential;.

Course Outline

Microsoft Excel

Introduction

  • Why use Excel Power Pivots
  • Excel PowerPivot Workbooks
  • The Excel PowerPivot tab
  • Data sources
  • Relationships

Importing Data

  • Using the Table Import Wizard
  • Import and Filter from Databases
  • Run SQL Query
  • Get Data from Files
  • Copy and Paste Data
  • Create Linked Tables

Excel PowerPivot Tables

  • Formatting Numbers
  • Handling Technical and Useless Columns
  • Understanding Calculated Columns
  • Using Lookup Tables
  • Understanding Measures
  • Handling Many Tables
  • Refreshing Data

Creating Relationships

  • Understanding relationships
  • Review existing relationships
  • Create new relationships

Building DAX Formulas

  • Formula basics
  • DAX Data Types
  • DAX Operators
  • Calculated Columns Examples
  • DAX Measure Examples
  • RELATED, CALCULATE functions

Excel PivotTables & PivotChart Reporting

  • Difference between Excel PivotTable and PowerPivot PivotTables
  • Updating the table
  • Changing the table structure
  • Formatting the PivotTable
  • Creating a PivotChart
  • PivotChart Layouts
  • Changing the Chart type
  • Formatting the Chart

Slicers

  • Adding Excel slicers to PivotTables and Charts
  • Resizing Slicers
  • Slicer options