Power Pivot & BI Tools


By using Power Pivot for Excel, you can transform enormous quantities of data with incredible speed into meaningful information to get the answers you need in seconds.

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


Courses, IT Skills

Related Products