This course has been revamped. Please check out new course IT091C: Transforming and Analyzing Data with Power Query and Power Pivot (2-Day course) for more information.

Programme Objective

Power Pivot is an add-in included within the Excel program to allow you perform powerful data analysis and create sophisticated data models. It works closely with the Excel Data Model to allow users to create pivot tables from multiple data sources. You can use Power Pivot to add data into Excel workbook and store it as an embedded Power Pivot database inside Excel file. You can then use this database in Excel PivotTable reports. Excel provides Power Pivot to help you organize, manipulate and create report on your data in the best way possible.

With Power Pivot, you can import millions of records from multiple data sources into a single Excel workbook, create calculated columns using Data Analysis Expressions (DAX) functions, create Data Model, create Key Performance Indicator (KPIs) to track performance against targets and create calculated measures that aggregate data from different rows on a PivotTable.

The use of Power Query and Power Pivot enable you to perform powerful data analysis, create interactive reports and make timely business decisions.

Programme Outline

 

• Understanding Power Pivot          
  • Overview of Excel Power Pivot    
  • Benefits and Drawbacks of Using Power Pivot  
  • The Difference between Power Pivot and Excel
  • Pivot Table Examples with Excel Data
  • Power Pivot Examples with a Data Model
  • Understanding the Power Pivot Internal Data Model
  • Limitations of the Internal Data Model    
  • The Power Pivot Window  
  • Understanding Types of Data Sources     

 

• Getting Data into Power Pivot     
  • Understanding Data Types
  • Linking Excel Tables to Power Pivot
  • Importing Access Tables
  • Importing Data from Excel File     
  • Adding and Maintaining Data in Power Pivot

 

 

 

• Creating the Data Model
  • Understanding Data Model
  • Understanding Key Fields
  • Understanding Table Relationships          
  • Creating Relationships between Tables in the Model
  • Managing Relationships
  • Creating and Using Linked Tables
  • Creating and Using Hierarchies

 

• Creating Calculations in Power Pivot
  • Types of Calculations in Power Pivot
  • Overview of Calculated Column and Field
  • Creating Calculated Columns using Formulas     
  • Creating Measure (Calculated Field)
  • Implicit Calculated Field
  • Explicit Calculated Field
  • Creating Calculated Column using Fields from another Table           
  • Understanding DAX Measure Rules         
  • Creating Measures using DAX Functions
  • Creating Key Performance Indicator (KPI)           

 

• Creating Power Pivot PivotTables
  • Creating Pivot Tables
  • Filtering Data using Slicers
  • Adding Visualizations to a Pivot Table
  • Creating Pivot Charts
  • Formatting Pivot Charts
  • Using Multiple Charts and Tables

 

• Building A Power Pivot Dashboard         
  • Understanding Dashboard 
  • Creating Power Pivot Dashboard  
  • Using Slicer and Timeline to Filter Data   

 

• Loading and Transforming Data with Power Query     
  • Introducing Power Query
  • Importing Data
  • Combining Multiple Files from a Folder
  • Splitting Column by Delimiters
  • Unpivoting Data
  • Filtering Data
  • Merging and Shaping Data
  • Grouping and Aggregating Data
  • Inserting Calculated Columns

Training Methodology

Lecture style, with hands-on exercises

Closing Date for Registration
1 week before programme or until full enrolment.
 

Intended For

This full day workshop is an introductory course for Power Pivot. If you need that additional flexibility in reporting not available in regular pivot tables, then this class focusing on Excel Data Models and Power Pivot is for you. Participants should have an adequate knowledge in PivotTable and PivotChart in order to benefit most from the course.

Programme Facilitator(s)