COMPUTER SKILLS
Computer skills are a valuable addition to any employee’s personal portfolio and can greatly increase your desirability to employers.
Overview
In this course, students will make use of the Power Pivot add-in to consolidate data from various Excel sources and create a dynamic report. Learn to become familiar with the Power Pivot application and Power Queries.
Objectives
- How to think like a database and understand Table Relationships
- Activating the Power Pivot Add-in
- Load data from other relational data sources, flat tables, text files, etc.
- Create and naming Excel Tables
- Adding and removing Tables from the internal data model
- Create calculated columns and KPI’s in Power Pivot
- Starting the Power Query and viewing the advanced Query Editor
- Importing Excel Workbooks, CSV, Text files, XML files and other data systems
- Using Power Query to transform data
- Adding conditional logic to custom columns
- Using the Append and Merge Feature
Course Prerequisite
Students must have successfully completed the MS Excel Intermediate and Advanced courses.
Computers to be equipped with MS Office Professional or Office 365.
Language of Delivery
English
Delivery Methods
Course is facilitated by a competent & experienced subject matter trainer, who utilises the following techniques to ensure that the session is practical and experiential:Â Questions; Discussion; Exercises & Assessments.
Who should attend?
This course is for advanced users of MS Excel who are looking for ways to enhance their skills in data analysis, pivoting & reporting areas.
Duration
1 Day
Includes:
- Comprehensive Manual
- Lunch & Refreshments (not applicable to on-site training)
- Electronic Certificate (on successful completion of the course)
- Electronic Trainer Feedback Report
- Electronic Delegate Feedback Questionnaire
Course outline
| Module 1 | Thinking like a Database Understanding Database Terminology Understanding Relationships between Tables |
| Module 2 | Introducing and Understanding Power Pivots Activating the Power Pivot Add-In Linking Excel Tables to Power Pivot Preparing Excel Tables Adding Excel Tables to the Data Model Creating Relationships between Power Pivot Tables Managing Existing Relationships |
| Module 3 | Introducing PivotTables Creating PivotTables Refreshing PivotTables Hiding Subtotals Understanding Slicers Creating and Modifying a Standard Slicer |
| Module 4 | Loading Data from a Relational Databases Loading Data from Microsoft Access Loading Data from Flat Files Loading Data from Text Files Loading Data from The Clipboard Loading Data from Other Data Sources Refreshing and Managing External Data Connections Manually Refreshing Power Pivot Data |
| Module 5 | Creating and Naming Tables Directly Feeding the Internal Data Model Limitations of Power Pivot driven PivotTables Adding a New Table to the Internal Data Model Removing a Table from the Internal Data Model Creating a New PivotTable using the Internal Data Model Internal Data Model with Multiple External Data Tables |
| Module 6 | Enhancing Power Pivot Data with Calculated Columns Create a Calculated Column Reference Calculated Columns in other Calculations Using DAX to create Calculated Columns Referencing Fields from other Tables Understanding Calculated Measures/Creating a Calculated Measure Free your Data with Cube Functions |
| Module 7 | The Power Query Add-In Starting the Query Understanding the Query Steps Viewing the Advanced Query Editor Managing Existing Queries Understanding Column Level Actions |
| Module 8 | Importing Data from Files Getting data from Excel Workbooks From CSV and Text Files From XML Files From Folders Importing Data from Database Systems ODBC Connections to Non-Standard Databases Getting Data from other Data Systems |
| Module 9 | Common Transformation Tasks Removing Duplicate Records Filling Blank Fields and Empty Strings Concatenating Columns Change the Case of Text Finding and Replacing Text Extracting the Left, Right and Middle Values Splitting Columns using Character Markers Pivoting and Unpivoting Fields Creating Custom Columns Understanding Data Type Conversions Adding Conditional Logic to Custom Columns Grouping and Aggregating Data |
| Module 10 | Making Queries work together Reusing Query Steps Understanding the Append Feature Appending Data Understanding the Merge Feature Merging Queries |