IT and Computing (Live Online) > Excel Power Tools: Data Transformation and Analysis with Power Query and Power Pivot

Excel Power Tools: Data Transformation and Analysis with Power Query and Power Pivot

Course Objectives

Basic knowledge of Windows is essential with the following pre-requisites:

Able to switch between task applications
Able to create a spreadsheet with simple formatting
Able to use Auto Filter command

Able to use basic functions – AutoSum, Count, Max, Min and Average functions

Audience

Sales Executive / Managers
Business Analysist
Resource Planning Team
Project Managers
Purchasing Manager
Financial Executive / Managers

METHODOLOGY

This program will be conducted with interactive lectures, PowerPoint presentation, discussion, and practical exercise.

COURSE OUTLINES

POWER QUERY

Lesson 1 : Getting data from multiple of source

Import data from Excel
Import data Table / Range
Import data from Text or CSV Files
Import data from Web
Import data from Folder

Lesson 2 : Managing Query

Duplicate Query
Rename Query
Switching Between Power Query Editor and Excel Screen
Viewing Applied Steps in Query Settings pane
Viewing details of Applied Steps in Formula Bar
Renaming and Deleting Applied Steps

Lesson 3 : Keep the Right Data

Identify Column Header via Promote and Demote
Changing Data Type
Choose Columns
Remove Blank Rows
Removing Duplicate Records
Remove Top/Bottom Rows
Moving, Renaming Columns
Reorganize data with Sorting
Get relevant data with Filter

Lesson 4 : Combine Table

Joining multiple tables into master table via Append Queries
Merging Queries on Single or Multiple Matching Columns
Returning Referenced Details on Those Matched
Returning Results on Those Not Matched

Lesson 5 : Getting Results

Close and Load data into Excel Table from Power Query
Refresh query to get latest data from source

Lesson 6 : Data Reshaping

Replace Value
Replace Error
Transpose Rows & Columns
Unpivoting Columns
Pivoting Columns
Splitting Column into Multiple Columns
Merging Columns
Fill empty cells with data above and below

Lesson 7: Output Different Result

Manage number column
- standard calculation
- rounding
Manage Text Column
- Converting Text to Uppercase, Lowercase
- Clean up non-printable characters with TRIM and CLEAN
- Adding Prefix / Suffix
- Extracting Part of Text
Manage Date Column
- Calculate Age
- Extract Year, Quarter, Month & Day
Summing Values based on Grouping

Lesson 8 : Managing Additional Column

Create Custom Columns

Create Conditional Column
Indexing Column
Column from Examples

Lesson 9 : Managing Data Source

Data Source Settings
Change Source Location
Change to Different Source

POWER PIVOT

Lesson 1 : Starting with PowerPivot

Add-ins Power Pivot from Options
Launch the PowerPivot window
A Tour to PowerPivot window
Exploring the PowerPivot tab
Changing between Data & Diagram view

Lesson 2 : Adding data to PowerPivot

Get Data from database
Get data from Excel files
Get data from Text or CSV files
Add Data by using Excel Linked Tables
Load from Power Query

Lesson 3 : Preparing data for analysis

Working with Tables
Managing Columns
Set Data Type and Format
Filtering and Sorting Data

Lesson 4 : Manage Relationships & Hierarchy

Understanding Types of Relationships
Create a Relationship Between Tables
View and Edit Relationships
Delete Relationships
Hierarchy
- Create & rename Hierarchy
- Adding Column to Hierarchy
- Remove Column from Hierarchy

Lesson 5 : Manage Connection

Refresh with latest data
Editing connection
Deleting connection

Lesson 6 : Calculated Column

Building Calculated Columns within a Table
Building calculate Column across multiple table

Lesson 7 : Measures

Creating a New Measure
Formatting a Measure
Useful DAX Functions
- PREVIOUS MONTH / PREVIOUS QUARTER
- TOTALYTD / TOTALQTD / TOTALMTD
- SAMEPERIODLASTYEAR
- CALCULATE
Modify and Delete Measures

Lesson 8 : Key Performance Indicators (KPI’s)

Create New KPI
Manage KPI

Lesson 9 : Date Table

Create a Date Table
Mark as Date Table

Lesson 10 : Pivot Table & Chart

Create a Pivot Table
Adding Data into Pivot Table
Formatting Pivot Table
- Report Layout
- Subtotal
- Grandtotal
- Pivot Table Style
Create a Chart
- Adding Data in Chart
- Formatting Chart
  - Style
  - Elements

Target Audience

This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.

Methodology

This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise

Course Modules

Module 1: Getting Different Views on Your Data
Topic A: Outlining and Grouping Data
– Using Automatic Outlining
– Displaying and Collapsing Levels
– Grouping Data Manually
– Creating Subtotals
Module 2: Planning, Forecasting & Projection of Your Data
Topic A: Using Data Analysis Tools
– Using a One or Two
– Input Data Table
– Using Goal Seek
Topic B: Exploring Scenarios
– What is a Scenario?
– Creating a Scenario
– Saving Multiple Scenarios
– Creating a Scenario Summary Report
Topic C: Using Solver
– Understanding Solver
– Generating Reports and Scenarios with Solver
– Changing Solver Values
– Managing Solver Constraints
– Using Solver as a Goal Seek Tool
Module 3: Advanced Functions
Topic A: Working with Array Formulas
– What are Array Formulas?
– Using Functions within Array Formulas
Topic B: Using the LOOKUP Function
– Using VLOOKUP to Find Data
– Find Exact Match with VLOOKUP
– Find Closest Match with VLOOKUP
– Find with HLOOKUP
Topic C: Alternative LOOKUP Function
– Using the INDEX Function
– Using the MATCH Function
– Combining the MATCH and INDEX functions
Topic D: Linking, Consolidating, and Combining Data
– Linking Workbooks
– Consolidating Workbooks
– Combining Worksheets
Module 4: Present Data with Visual Report
Topic A: Create Combo Chart
– Comparing 2 Types of Values with high Variance
– Creating Combo Chart
– Editing Combo Chart
Module 5: Summarizing and Analyzing Data with PivotTable, Slicer and PivotCharts
Topic A: Create a PivotTable
– Create PivotTable Dialog Box
– PivotTable Fields Pane
– Summarize Data in a PivotTable
– Show Values as Functionality of a PivotTable
– Personalize the default PivotTable layout
Topic B: Filter Data by Using Slicer
– Slicers
– Insert Slicers Dialog Box
Topic C: Analyzing Data with PivotChart
– Creating PivotChart
– Editing a PivotChart
Module 6: Navigating Large Workbooks
Topic A: Customizing Workbooks
– Inserting Hyperlinks
– Editing Hyperlinks
– Formatting Hyperlinks
– Using Hyperlinks in Excel
Module 7: Macros
Topic A: Macros
– Recording Macros
– Saving to Macros Enabled Workbook
– Closing and Re-Opening a Macro Workbook
– Security Warning Message
– Playing a Macro
– Assigning a Keystroke to a Macro
Module 8: Prevent User Changes with Protection
Topic A: Protecting Worksheet and Workbook
– Protecting the Current Sheet
– Protecting an Entire Workbook
– File Protection

Get To Know The Trainer

Trainer P.L

4.5

She is an expert on Microsoft Applications, Publisher (Version 2000, XP, 2003 & 2007, VBA, Introduction To Internet, Multimedia Fundamentals, IT Skills For Non-PC Users, Basic IT skills and related programs. In her previous company, she was responsible for developing training courseware and customizing training curriculum.

VIEW PROFILE

Trainer Jarina

4.5

Managing and maintaining a Microsoft Windows Server 2003 Environment - educate the IT professionals who implement, manage, and troubleshoot existing network and system environments based on the Windows Server 2003 platform. Implementation responsibilities include installing and configuring parts of systems. Management responsibilities include administering and supporting systems

VIEW PROFILE

Trainer Azrul

Azrulnizam Ahmad has been working in the business industry since 2004 and has been exposed to all the common challenges faced by business personnel. With his years of experience working with business and management, IT services and end users, he understands the importance of business value, infrastructure efficiency and user productivity.He shares his training passion in Microsoft Office 2007 Application - Excel, PowerPoint, and Word; Microsoft Office 2010 Application - Excel, PowerPoint, and Word and also emphasizes on the criticality of this aspect in any implementation

VIEW PROFILE

Trainer Nazarudin

4.5

Nazarudin Aziz started freelancing as a Corporate Trainer since the year of 2005 specializing in the field of Multimedia Graphic Designer and Microsoft Office Applications. His list of clients are Perodua, Grand Pacific Hotel, Cosway, UDA Mall, Institut Teknologu Rekabentuk Perabot Malaysia, Telekom Malaysia, Lembaga Tabung Haji, Yayasan Pelajaran MARA, Kementerian Pembangunan Wanita & Masyarakat, Hospital Tengku Ampuan Rahimah Klang, United Engineers Malaysia, Malaysian Maritime & Dredging Corporation, Jabatan Perdana Menteri, Indah Water Consotrium and many more.

VIEW PROFILE

Rahul

4.5

Rahul Thomas graduated in Bachelor of Information Technology majoring Computer Security and currently pursuing in Master of Information Technology majoring in Cybersecurity. He is an internationally certified trainer with successful years of experience in Cybersecurity, Cloud Computing and Microsoft Office. He is also recognized for his unconditional consistent in performance excellence and contributions to success in the Information Technology industry. Besides that, he also has a very enthusiastic personality when it comes to training, and he has been recognized for disseminating remarkable trainings.

VIEW PROFILE

Trainer Siva

4.5

Siva is a highly motivated, customer focused, IT / Web / Multimedia professional / Microsoft Office. Siva has excellent communication, interpersonal, and leadership skills. Siva is knows to have strong analytical and troubleshooting skills. He is also very effective at conceiving and initiating new ideas and strategies. Ability to work productively with a wide range of people but can also work well independently. An enthusiastic team player, who works well under pressure, is extremely focused, and is able to deliver high quality of service within tight deadlines.

VIEW PROFILE

View more trainers