Microsoft Excel PowerPivot

Course Objective

This course is about bringing the power of BI (Business Intelligence) to users with the introduction of PowerPivot for Excel. It is designed to help you take the necessary steps to transform you from an Excel user to a self-service BI modeler. The results you will be able to accomplish are invaluable.

 

  • Learn the basics of the PowerPivot tool
  • Learn how to shape your data so that you can execute analysis efficiently
  • Learn the DAX language
  • To have a great background in the new modeling options of Excel
Course Duration

2 Days

Who Should Attend
  • Sales & Marketers
  • CFO, Treasurers, Financial Controllers, Accountants
  • Project Managers & Engineers
  • General Managers
Prerequisites

This course is designed for current Microsoft Excel users who can build basic workbooks. It is assumed that the users know how to create and save a workbook, create formulas and use functions, and perform simple formatting of the data.

Course Modules
  1. First Steps with PowerPivot

1.1    Working with Classic Excel PivotTables

1.2    Working with PivotTables in PowerPivot

1.3    Importing Data

1.4    Querying Data

 

  1. PowerPivot at Work

2.1    Using the PivotTable to Produce Reports

2.1.1    Formatting Numbers

2.1.2    Hiding or Removing Useless Columns

2.1.3    Adding Calculated Columns

2.1.4    Adding Measures

2.2    Adding More Tables

2.3    Working with Dates

2.4    Refreshing Data

2.5    Using Slicers

 

  1. Introduction to DAX

3.1    Understanding Calculation in DAX

3.1.1    DAX Syntax

3.1.2    DAX Data Types

3.1.3    DAX Operators

3.1.4    DAX Values

3.1.5    Understanding Calculated Columns and Measures

3.1.5.1 Calculated Columns

3.1.5.2 Measures

3.1.6    Handling Errors in DAX Expressions

3.1.6.1 Conversion Errors

3.1.6.2 Arithmetical Operations

3.1.6.3 Intercepting Errors

3.1.7    Common DAX Functions

3.1.7.1 Statistical Functions

3.1.7.2 Logical Functions

3.1.7.3 Information Functions

3.1.7.4 Mathematical Functions

3.1.7.5 Text Functions

3.1.7.6 Date and Time Functions

 

  1. Data Models

4.1       Understanding Data Models

4.1.1    Following the Standard Excel Method

4.1.2    Discovering the PowerPivot Way

4.1.3    What Is a Data Model?

4.2       Understanding Physical and Logical Data Models

4.2.1    Normalization and Denormalization

4.2.2    Empty Values

4.3       Understanding How and When to Denormalize Tables

4.3.1    The PowerPivot Query Designer

4.3.2    When to Denormalize Tables

4.4       Complex Relationships

4.5       Understanding OLTP and Data Marts

4.5.1    Data Marts, Facts, and Dimensions

4.5.2    Star Schemas

4.5.3    Querying the Data Warehouse

4.6       Discovering Advanced Types of Relationships

4.6.1    Role-Playing Relationships

4.6.2    Many-to-Many Relationships

 

  1. Loading Data and Models

5.1       Understanding Data Sources

5.2       Loading from a Database

5.2.1    Loading from a List of Tables

5.2.2    Loading Relationships

5.2.3    Selecting Related Tables

5.2.4    Loading from a SQL Query

5.2.5    Loading from Views

5.3       Opening Existing Connections

5.4       Loading from Access

5.5       Using Linked Tables

5.6       Loading from Excel Files

5.7       Loading from Text Files

5.8       Loading from the Clipboard

 

  1. Evaluation Context and CALCULATE

6.1       Understanding Evaluation Context

6.1.1    Filter Context in a Single Table

6.1.2    Row Context in a Single Table

6.1.3    Adding Filters to a Filter Context for a Single Table

6.1.4    Removing Filters from a Filter Context for a Single Table

6.1.5    Row Context with Multiple Tables

6.1.6    Filter Context with Multiple Tables

6.1.7    Modifying Filter Context for Multiple Tables

6.1.8    Final Considerations for Evaluation Context

6.2       Understanding the CALCULATE Function

6.3       Understanding the EARLIER Function

 

  1. Date Calculations in DAX

7.1       Working with a Dates Table

7.1.1    How to Build a Dates Table

7.1.2    Working with Multiple Dates Tables

7.1.3    Differentiating Columns in Multiple Dates Tables

7.1.4    Calculating Working Days

7.2       Aggregating and Comparing over Time

7.2.1    Year-to-Date, Quarter-to-Date, and Month-to-Date

7.2.2    Periods from the Prior Year

7.2.3    Difference over Previous Year

7.2.4    Simplifying Browsing with a Period Table

7.3       Closing Balance over Time

7.3.1    Semiadditive Measures

7.3.2    OPENINGBALANCE and CLOSINGBALANCE Functions

7.3.3    Updating Balances by Using Transactions

    WELCOME TO LCCM ASIA

    Master new skills, LCCM Asia simply empowering everyone with a growth mind-set in today’s digital world.

    Want to learn more about our corporate training programs?

    Contact us today!