PowerPivot for Power Users

Advanced Excel users, users that want to gain familiarity with PowerPivot to build compelling reports or working with data model.

Successful learners will have prior knowledge and understanding of Excel 2016 or Excel Dashboard & Reports course.

Explore every aspect of using Microsoft PowerPivot.

Module 1: Power Query Editor

In this module we take a look at all the possible data sources of which PowerPivot can work with!

Lessons

• Supported Data Sources

Lab 1: Loading Data Sources

Lab 2: Cleaning Data

• Cleaning data in PowerPivot
• View or Change data source settings
• Exercise 1: Find data anomalies and data statistics
• Exercise 2: Shape the initial data
  • Renaming tables
  • Changing data type
  • Splitting columns
  • Renaming columns
  • Filling empty values
  • Using Column from Examples to split columns
  • Removing unwanted rows: Top Rows, bottom rows
  • Use First row as header
• Exercise 3: Data Mashup
  • Appending queries
  • Merging Data
• Exercise 4: Changing the Data Structure
  • Transpose data
  • Unpivoting Tables
• Exercise 5: Filtering dataset
• Exercise 6: Load data to Excel Data Model

Module 2: PowerPivot Functions

In this module we take a look at PowerPivot Functions/DAX Expressions and how to use them!

Lab 0: Creating Relationships

• Create Relationship between Data Sources

After completing this module, students will be able to:

• Import various data sources into PowerPivot
• Understand how to clean imported data

Lab 1: Create a Time Table

• Create a Time Table for future labs

Lab 2: Using STATISTICAL, LOGICAL Functions

• Exercise 1: Simple Calculations
• Exercise 2: Calculating Across Tables (RELATED)
• Exercise 3: Simple Logic: The IF(), AND(), OR() Function

Lab 3: Using STATISTICAL Functions

• Use SUM, AVERAGE* Functions
• Use COUNT*, MIN*, MAX* Functions

Lab 4: Using FILTER Functions

• Use ALL, ALLEXCEPT
• Use CALCULATE

Lab 5: Examine filter context

• VALUES(), HASONEVALUE()

Lab 6: Use SWITCH(), VALUES()

• Use Slicer for selecting measures

Lab 7: Using TIME INTELLIGENCE Functions

• YEARTODATE, QUARTERTODATE, AND MONTHTODATE CALCULATIONS
• Calculating Sales for The Previous Year: PREVIOUSYEAR,
• Comparisons overtime: SAMPEPERIODLASTYEAR, DATESINPERIOD

Module 3: Visualization skills

In this module we take a look at building reports using PowerPivot.

Lessons

• PowerPivot Reports
• Adding KPIs
• Conditional Formatting

Lab: Creating Reports

• Use Pivot tables, PivotCharts
• Adding KPIs
• Sync Slicers
• Embedded multiples pivot tables / pivot chart into single sheet