This Excel Power Pivot Introduction course shows how you can generate rich and interactive Power Pivot reports that look like they have been weeks in development.
The Excel Power Pivot feature provides Business Intelligence (BI) functionality and reporting on a scale previously unimaginable with pivot tables e.g. you can process millions of rows of data. It provides deeper business insights, in a fraction of the time, using familiar Excel tools.
What will you learn?
At the end of this Excel Power Pivot Introduction course, delegates will be able to:
- Understand how Power Pivot builds on PivotTables, slicers and key analytical functions
- Import data, from a variety of sources, and quickly relate them to deliver key information
- Write powerful formulae in the DAX language
- Create Dashboards, to visually display your data, using Power View
Audience?
Anyone using Excel to create complex reports, or needing advanced data analytics to process, analyze and display data.
Course Contents
Overview
- Why use Power Pivot?
- Navigating the interface
Importing Data
- Importing from various Data sources
- Data Connections
- Refreshing Data
Building Data Models
- Create relationships between tables
- One-to-Many Relationships
- Deriving relationships from data sources with the Relationships tool and the Diagram View
Fixing common data issues with Power Query
- Extracting, Transforming and Loading (ETL) data
- Converting data formats with Power Query steps
- Parsing columns to aid analysis
- Removing duplicates
- Create a single data set from multiple sources with the same field headings
Excel Tables
- Why Excel Tables are important
- Using Slicers to filter data
- Adding Linked Tables to the Data Model
Excel Pivot Tables Recap
- Understanding the structure
- Changing the Layout
- Using filters
- Grouping data for ad-hoc subtotals
- Grouping dates
Creating PivotTables with PowerPivot
- What’s different in a PowerPivot Pivot Table?
- Creating a PivotTable with Power Pivot data
- Using the Field list with the Data Model
- How Drill-down differs in Power Pivot
- Creating a flattened Pivot Table to analyse combinations of unique data
Working with Dates
- Why a Date (Calendar) Table is required
- Generate a Date Table
- Mark as a Date Table
- Grouping dates for time analysis
- Comparing and categorising time periods with Time Intelligence functions
Using Slicers to Filter Data
- Using Slicers
- Connecting Slicers to more than one PivotTable
Building DAX (Data Analysis eXpressions) Formulas
- Formula basics
- DAX Data Types
- DAX Operators
- Defining Calculated Columns
- DAX Measure Examples
- RELATED(), CALCULATE(), SWITCH()
KPIs – Setting key business targets
- Analysing performance with calculated fields
- Gauging performance against goals
Dashboards
- Articulating and analysing data
- Drilling down into data using a hierarchy
- Managing data with perspectives
- Identifying patterns/trends in Power Pivot data with Power View charts
- PivotTable slicers and Power View filters
Course Materials
Enliten IT will provide each delegate with a workbook and other useful reference materials where applicable. These may be either paper based or in Adobe pdf format or a combination thereof.
“"Excellent session made fun!"”
Liz, Cashflows, Excel Intermediate
Prerequisites
Delegates must have attended the Excel Advanced course or have equivalent experience and understand PivotTables.
Additional Information
NOTE: We only offer the Microsoft 365 Excel Power Pivot Introduction course on a private in-house basis.
Enliten IT reserves the right to modify course content and materials as required in the interests of continuous course improvement, at any time, without notification.
The suggested course duration is a guideline. Course topics and duration may be modified by the instructor based upon the knowledge and skill level of the course participants.