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 2024
 
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-7498 7200
 
 
Information & Communication Technology
Power Query: Transforming Your Work Processes using Excel
 
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 -