Microsoft Excel Power Query

27 Nov, 2024, AC Hotel Kuala Lumpur by Marriott

Register Now
PDF Brochure

IPA Training is Registered with

Learn from the Best

Theresa Morgis
Microsoft Application Trainer

 

Theresa Morgis is certified Microsoft Trainer and a certified HRDF trainer. She has more than 20 years of training experience in Microsoft products. Theresa is well versed in Microsoft Application products: MS Excel, Word, and PowerPoint (Basic to Advance Level).

 

Her years of experience in this field has also encouraged her to develop and customize her own training materials and she occasionally customise specific course manuals upon requests.

 

Theresa has conducted training for CEO’s, vice presidents, directors, accountants, managers, secretaries,
production operators and clerical staffs.

 

Her Clients include Shell Malaysia, ASTRO, Bolton Bhd, SMBC Bank, Pos Malaysia, TNB, Petronas, Honda, Ingram Micro, Maybank Bhd, Bank Simpanan Nasional, Telekom Malaysia, Nippon Oil, RHB Bank, Public Bank Bhd, CIMB, Hilton Hotel, Touch N Go, PLUS, Ansat Broadcast, Sunway University, Monash University, UNITAR, Ambank, Zuellig Pharma, HERO and more.

 

Venue Details

AC Hotel Kuala Lumpur by Marriott
9 Jalan Lumut off Jalan Ipoh, Kuala Lumpur Malaysia, 50400,
Phone : +60 3-40428000

https://www.marriott.com/en-us/hotels/kulka-ac-hotel-kuala-lumpur/overview/?scid=45f93f1b-bd77-45c9-8dab-83b6a417f6fe&y_source=1_MTQ2 NDU5MDEtNDgzLWxvY2 F0aW9uLndlYnNpdGU%3D

Contact us

Juliany,
03 2283 6109
juliany@ipa.com.my

Phoebe,
03 2283 6100
phoebe@ipa.com.my 

FOR CUSTOMISED IN-HOUSE TRAINING
Jane,
03 2283 6101
Jane@ipa.com.my

ADDRESS 
A-28-5, 28th Floor, Menara UOA Bangsar, 
No.5, Jalan Bangsar Utama 1, 
59000 Kuala Lumpur
www.ipa.com.my

FOCUSING ON
  • Module 1: Introducing Power Query
  • Module 2: Types of Query Connections
  • Module 3: Transforming Data with Power Query
  • Module 4: Creating Custom Columns
  • Module 5: Table Transformations
  • Module 6: Loading Data to Excel
  • Module 7: Manage Query Editor
COURSE OVERVIEW
Power Query is a business intelligence tool available in Excel that allows you to access and import large corporate data from many different sources into the data model in a single Excel workbook. With the Power Query Editor, you can clean the raw data and transform it, then reshape it according to your needs.
AFTER ATTENDING THIS COURSE YOU WILL RETURN TO YOUR JOB…
  1. Importing and transforming data from multiple sources.
  2. Learning about data model creation.
  3. Combining data from folders and multiple worksheets.
  4. Merging data without having to use VLOOKUP, INDEX and MATCH functions.
  5. Analysing and Producing Reports.
WHO SHOULD ATTEND
Anyone who frequently uses Excel for data analysis, reporting, or data management will find Power Query invaluable. It simplifies complex data transformation tasks and automates repetitive processes.
METHODOLOGY
This course is a Instructor Led Training – interactive lectures which includes discussions and practical exercises.
COURSE CONTENT
9:00

MODULE 1: INTRODUCING POWER QUERY

  • Overview of Excel Power Query
  • Power Query Editor Ribbons
  • Creating a simple Query
  • Understanding Query Steps
  • Refreshing Power Query Data
 

MODULE 2: TYPES OF QUERY CONNECTIONS

  • Extracting Data from Excel files
  • Getting data from CSV & Text files
  • Getting data from folders
  • Importing Data from Database System
10.45

MODULE 3: TRANSFORMING DATA WITH POWER QUERY

  • Removing duplicate records
  • Changing Case
  • Trimming and cleaning text
  • Finding and replacing specific text
  • Extracting left, right and middle values
12.00

MODULE 4: CREATING CUSTOM COLUMNS

  • Splitting Columns
  • Naming Columns
  • Concatenate Columns
  • Custom Columns with functions
  • Adding conditional logic to Custom Columns
1:00 Lunch
2:00

MODULE 5: TABLE TRANSFORMATIONS

  • Pivoting and Un-Pivoting fields
  • Transposing a Table
  • Sort and Filter Data
3:00

MODULE 6: LOADING DATA TO EXCEL

  • Loading Data into a Worksheet
  • Loading Data into the Excel Data Model
  • Power Query and Table Relationships
3:45

MODULE 7: MANAGE QUERY EDITOR

  • Edit Query Steps
  • Edit Query Step Settings
  • Duplicating Queries
  • Refresh a Query
5:00 End of Course