Vision & Mission
Upcoming Events
Training Programs
 
Industrial Relations
HR Management
& Development
Occupational Safety
& Health
Business Management
ICT
Certification &
Academic Courses
SWINBURNE
Certified Programs
UNIMAS
Certified Programs
In-house Courses
PSMB Scheme
Academy Staff
Contact Us
 
Training Directory 2019
 
MEF ACADEMY
3A06-3A07, Block A,
Phileo Damansara II,
No. 15 Jalan 16/11, Seksyen 16,
46350 Petaling Jaya,
Selangor Darul Ehsan,
Malaysia

Tel: 603-7955 7778
Fax: 603-7955 1945
 
 
Upcoming Trainings & Events
 
Power Query: Transforming Your Work Processes using ExcelMidvalley City, KL 23 Sept, 7 Oct 2019
Download Registration Form
 
WHAT IS MICROSOFT EXCEL POWER QUERY?
Excel Power Query tool enables you to extract, transform and load data (ETL) very effectively. In this course, you will aquire the knowledge and skills in using Power Query which is far simpler, straight forward and easy to use when you need to extract, consolidate, merge data and automate work processes in Excel. Heavy users of Microsoft Excel will find this course very practical and useful. Basic knowledge of Microsoft Excel is assumed.
 
Objectives
 
Topics Covered
LESSON 1 – INTRODUCTION TO POWER QUERY
  • Introduction
  • Excel 2010/2013/2016
 
LESSON 2 – GETTING STARTED WITH POWER QUERY
  • Extract
  • Transform
  • Load
  • Edit
  • Refresh
  • Appending Files
 
LESSON 3 – IMPORTING EXCEL DATA
  • Connecting to Excel Tables
  • Connecting to Named Ranges
  • Connecting Dynamic Named Ranges
  • Consolidating Excel Tables
  • Consolidating Ranges and Worksheets
 
LESSON 4 – CONSOLIDATE FILES IN A FOLDER
  • Consolidate Excel Files
  • Consolidate CSV files
  • Adding More Files
  • Importing File Name as a Data Column
 
LESSON 5 – “UNPIVOT” PIVOT DATA
  • Set up your pivoted data as a table
  • Load table data in to Power Query
  • Unpivot Data
 
LESSON 6 – TRANSPOSING AND UNPIVOTING COMPLEX DATA
  • Transpose Data
  • Unpivoting Subcategorized Tables
  • Transposing Stacked Tables
 
LESSON 7 – IMPORTING NON DELIMITED TEXT FILES
 
 
LESSON 8 – MERGING TABLES AND QUERIES
  • Case Study 1
  • Case Study 2
 
LESSON 9 – IMPORTING FROM DATABASES
  • From SQL Server Database
 
LESSON 10 – OTHER POWER QUERY DATA SOURCES
  • Web Data
  • OData Data Sources
  • Data from Microsoft Exchange
  • Data from Facebook
  • Data from SAP
 
LESSON 11 – DATA DESTINATIONS
  • Query Loading Options
  • Query Loading Methods
  • Changing the Default Query Loading Settings
 
LESSON 12 – GROUPING & SUMMARIZING
  • Get Distinct Values
 
LESSON 13 – AUTOMATING REFRESH
  • Example with a csv file
  • Refresh the connection
  • Change the source
  • Refreshing a Single Connection
 
LESSON 14 – POWER QUERY FORMULAS
  • 5 Very Useful Text Formulas
  • IF Scenarios
  • Split Scenario using Text functions
 
 
Target Audience

 
Methodology
  • Lecture
  • Demonstration
 
Scheme SBL Fee RM 954
Duration 7 Hours Members Less -
 
This course will also be conducted at the following dates/venues
 
There are currently no events scheduled for other dates or venues.