Microsoft Excel PowerPivot

2 Days

• Sales & Marketers
• CFO, Treasurers, Financial Controllers, Accountants
• Project Managers & Engineers
• General Managers

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.

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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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