Duration 2 Days
Course Content
- What is Power Pivot ?
- What are the benefits of using Power Pivot ?
- How to enable the Power Pivot add-in
- The Power Pivot Window
Importing Your Data and the Data Model
- How to import data into the PowerPivot Data Model.
- Importing Data from a variety of Data Sources
- Working with Data Connections
- Refreshing Data
Creating the Power Pivot Data Model
- How to create relationships between tables in the PowerPivot Data Model.
- Understanding One-to Many Relationships
Excel Analysis Tables
- What is an Excel Analysis Table?
- Using Slicers to filter data in the Table
- Why Excel Tables are important to Power Pivot
- Adding Linked Tables to the Data Model
Analysing Data using a Pivot Table
- Understanding Compact Layout
- Changing the Layout
- Using the in-situ filters
- Grouping data for ad-hoc subtotals
- Grouping date columns into years, months
Creating PivotTables with PowerPivot
- How to create a PivotTable with PowerPivot data
- How to use the Field list with the Data Model
- What is different in a PowerPivot Pivot Table
- How Drill-down differs in PowerPivot.
- Creating a Flattened Pivot Table to analyse combinations of unique data.
Working with Dates in Power Pivot
- How to analyse by Year, Quarter, Month etc.
- Why a Date Table (Calendar Table) is required
- Generating & Marking as a Date Table.
Using Slicers to Filter Data
- How to filer data using Slicers
- Connecting Slicers to multiple Tables
Using Pivot Charts and Dashboards
- What is different about PowerPivot Charts
- Connecting Slicers to Pivot Charts
- Creating “Dashboards”.
Creating Simple Calculated Columns
- How to create a Calculated Column
- Looking up values from related tables
- What is a Calculated Field
- How to Add a Calculated Field to a PowerPivot PivotTable
- What are PowerPivot Sets?
Power Query
- Adding Data to Power Query from various data sources
- Creating a Query, the Query Editor Window
- Load to Worksheet and Load to Data Model
- Remove Duplicate Data in Columns
- Split and Merge Columns of Data
- Group data to create summary Queries
- Merge and Append Queries
- Transpose Data to swap Rows and Columns
- Filling data Down populating missing values
- Unpivot Columns: flatten a cross tab table
- Duplicate Columns
- Creating Custom Columns and Additional Date Columns
- Working with Applied Steps
- Importing Data from a Folder
Power View
- The Power View Field List
- Using Text Boxes
- Creating Tables
- Changing Table Visualisations
- Using a Matrix and Enable Drill Down
- Using Cards; Setting up Titles and Pictures
- Creating Chart Visualisations and Interactive Charts
- Creating Power View Slicers
- Using Tiles
- Using Power View Filters
- Working with interactive Bubble Charts using the Play Axis
- Creating Power View Maps
Power Map
- Create a Power Map & Plot Geographic Data
- Creation Visualisation; Column, Bubble, Heatmap, Regions
- Using the 3-D Environment
- Using Charts with Power Maps
- Working with Layers and adding Scenes
- Adding Annotations
- Visualising Data over Time
- Playing the Tour