Power Pivot for Microsoft Excel

DURATION

3 days

LOCATION

Exeter, Bristol, Devon, Gloucester or onsite at your premise.

OVERVIEW AND OBJECTIVES

Data analytics and self-service BI (Business Intelligence) have come a long way in recent years, and there is an ever-increasing demand for more effective tools to mine large amounts of data. To address these requirements Microsoft designed Power Pivot as an add-in to Excel. Power Pivot can in one way be viewed as Excel on steroids. The additional power and flexibility that Power Pivot affords can take your data analysis skills to unheralded new heights.

This course is suitable for existing Excel users who have to manipulate, analyse and report on large amounts of data. For example, business analysts, finance teams, data scientists, and revenue managers, to name but a few. Essentially, anyone who has data and needs to gain insights into that data.

In this course, students will gain the knowledge and skills necessary to import and merge large amounts of data, summarise and aggregate data, present strategic data, provide better business insights, and empower informed decision-making. You will also learn how to mine and expose hidden information with the DAX (Data Analysis eXpressions) language.

The course assumes no previous knowledge of Power Pivot or DAX and is designed from the ground up to gradually introduce delegates to this new and powerful technology. There is a linear progression throughout the training from the initial introductory material through to more intermediate and advanced material. As such, this course is modularised in such a way that delegates can take just the first 2 days of the course and omit the final day should they find the more advanced topics beyond their needs.

COURSE PREREQUISITES

Delegates should have some general Excel and reporting skills.

It would also prove beneficial, although not essential, if students possess some familiarity with coding expressions, such as in Excel formulae.

Course Objectives

Upon successful completion of this course, students will be able to:

  • Understand the role Power Pivot plays in data analytics
  • Understand the role of other Power Pivot components
  • Load data into a Power Pivot data model
  • Create and use calculated columns
  • Create and use measures
  • Understand how to use and alter filtering contexts
  • Work with multiple dimensional tables
  • Understand time intelligence and work with dates in pivots
  • Make decisions in DAX expressions
  • Understand iterator functions
  • Work with multiple fact tables
  • Understand the concept of granularity
  • Understand the role of Power Query
  • Work with complex table relationships
  • Create and use custom calendars
  • Create and use DAX variables

SUBJECT AREAS

(PLEASE NOTE, subject areas will depend on delegates availability and time available.)

DAY 1 & 2: INTRODUCTORY TOPIC

Introduction to Power Pivot

  • The Need for Power Pivot
  • The Components of the Power BI Family of Tools
  • Power Pivot Support in Excel

Loading Data into Power Pivot

  • Introducing the Power Pivot Window
  • Overview of Supported Data Sources
  • Linked Tables as a Data Source
  • Pasting Data into Power Pivot
  • Importing Text File Data
  • Databases as a Data Source
  • Tips and Tricks

Calculated Columns 

  • Adding a Calculated Column
  • Inputting a Formula
  • Renaming Columns
  • Properties of Calculated Columns

Introducing DAX Measures

  • Adding a Measure
  • Creating a Pivot Table from the Data Model
  • Using Slicers
  • Measures that Use Other Measures
  • Benefits of Measures
  • DAX Expressions to Count Rows
  • How DAX Measures Work

The CALCULATE Function

  • Introducing the Concept of Filters
  • Using the CALCULATE Function
  • Filter Operators
  • Removing Filters with ALL
  • The ALLEXCEPT and ALLSELECTED Functions

Working with Multiple Tables

  • Data Modelling Concepts
  • Why have Multiple Tables?
  • Table Relationships
  • Filters Across Multiple Tables
  • Disconnected Tables

The FILTER Function

  • When Should You Use FILTER?
  • Using the FILTER Function
  • Working with Dates (Time Intelligence)
  • What is Time Intelligence
  • Calendar Tables
  • Time Intelligence Functions

Conditional and Branching Logic

  • Using the IF Function
  • Checking for the Existence of Data
  • The VALUES Function

Iterator Functions

  • The Purpose of Iterator Functions
  • The SUMX Function
  • Other Aggregate Iterator Functions

DAY 3: ADVANCED TOPICS (OPTIONAL)

Working with Multiple Data Tables

  • Considerations for Working with Multiple Data Tables
  • Data Tables with Differing Granularity

Cleaning and Shaping Data with Power Query

  • The Role of Power Query in Power Pivot
  • Appending Files to Create a Single Power Pivot Table
  • Combine Multiple Files into a Single Table
  • Adding Custom Columns to Your Lookup Tables
  • Using Power Query to Unpivot a Table
  • Using Power Query to Create a Lookup Table
  • Creating a Calendar Table

Complex Table Relationships

  • Multiple Relationships Between the Same Two Tables
  • The USERELATIONSHIP Function
  • Many to Many Relationships

Custom Calendar Tables

  • Introducing Custom Calendar Tables
  • Creating Measures to Work with Custom Calendars

Advanced and Newer DAX Functionality

  • The SWITCH Function
  • The RANKX Function
  • The TOPN Function
  • The INTERSECT Function
  • The EXCEPT Function
  • The UNION Function
  • Using DAX Variables

WHAT’S INCLUDED?

Courseware and manual, certificate of achievement, refreshments and lunch are all included in this course.

To Book

For more information or to book onto our Pivot for Microsoft Excel training course please contact us on 01392 824 022 or email training@amesolutions.co.uk.

If there is an Microsoft Excel Training course that is not listed on our site, that you are interested in attending then please let us know and we can arrange one to one training onsite with our Trainer.

You can see a full list of our training courses here.

Still not convinced?
We can help you!

Fill out the form below and one of our IT specialists will contact you.





Testimonials

PhoneEmailTwitterAME Support ToolApple Mac Support tool