INTRODUCTION FOR POWERFUL DATA ANALYSIS AND DATA MODEL WITH POWER PIVOT
Power Pivot is an Excel add-in that provides some of the most powerful features for those are dealing with big data. With its own DAX functions, it can be used to perform multiple calculations and complicated data analysis, enabling you to create sophisticated data models.
This course, goes beyond the basics, to enable you to become more comfortable with Power Pivot models, using Pivot tables and charts, as well as carrying out calculations and time intelligence functions.
TARGET AUDIENCE
This course is for individuals whose job responsibilities include creating complex Excel reports, as well as conducting advanced analytics and data science.
COURSE OBJECTIVES
After completing this course, students will be able to:
- Use Power Pivot in Excel for powerful data analysis
- Differentiate Power Pivot & Pivot Tables
- Create Calculated Columns and Measures
- Write DAX expressions to perform calculations
create Relationships between tables and using relationship calculations - Create and use hierarchies
- Create simple models in Power Pivot, loading data from different data sources
- Create Reports and KPI
PRE-REQUISITE
It is recommended that you have a good understanding of Excel’s standard Pivot tables and charts before taking this course.
COURSE CONTENT
Importing Data into Excel
- Introduction to Relational Databases
- Introduction to Queries
- Importing Access Data
- Importing Web Data
Power Pivot and the Data Model
- Introduction to Excel Power Pivot
- Understanding the Excel Data Model
- Advantages of the Excel Data Model over Pivot Tables
- Activating Excel Power Pivot
- Loading Data into the Data Model
- Browse, Filter and Sort
Power Pivot & Pivot Tables
- Building Pivot Tables with the Excel Data Model
- Building Pivot Tables in PowerPivot
- Creating Simple Measures in the Data Model for Pivot Tables
- Using Slicers and Filtering Data with Power Pivot
DAX Language in Power Pivot
- Introduction to DAX in Power Pivot
- Creating Calculated Columns with DAX
- Using the RELATED and RELATEDTABLE Functions
- Creating Calculated Fields with DAX
- Understanding Pivot Table Evaluation
- Using Date Functions in Power Pivot
Measures in the Data Model
- Introduction to Measures
- The =CALCULATE Formula
The ALL and ALLEXCEPT Formulas - Using SUMX Functions
- RANKX Function
- Customer Segmentation (The SWITCH Function)
Relationships
- Introduction to Relationships
- Relationship Calculations
- One-to-one Relationship
- One-to-many Relationship
Sets and KPIs
- Introduction to KPIs and Sets
- Creating Sets
- Creating Hierarchies
- Creating KPIs
- Using a Parameter Table in PowerPivot
