This course is conducted in Windows operating system. There will be no laptop provided. Learners are required to bring along their own laptops.

2-Day Excel Power Query Series 2 - Automate Complex Data Transformation Course
In Singapore (New Launch & Exclusive)

Automate complex data transformations beyond Excel Power Query interface limitations, and effortlessly solve problems using powerful and useful Power Query functions

All our courses are not subsidised and non-claimable using SkillsFuture credits

Excel Power Query Series 2 - Automate Complex Data Transformation Course Overview

This course is created, owned, and trained by its original creator,  Mr. Alaster Leong. Learn from the original creator.

Important Note: 

Please check the following before registering:

  • The guide to check Power Query availability here.

For users who have already attended our ‘Excel Power Query Series 1 – Automating Data Preparation‘ or regular Power Query users who want to take their Power Query skills to the next level, this course is for you!

In this hands-on course, you’ll learn techniques on how to deal with complex data transformation challenges that the Power Query interface cannot provide a solution for.

Here are some of the challenges you may be facing:

  • Connecting to a folder of files with different data structure
  • Connecting to a folder of files with extra columns in some workbooks
  • Connecting to a folder of files with different header names
  • Having two header rows in your data listing
  • Non-structured data (e.g. data in data entry form format)
  • Creating unique ID for each data group
  • Removing extra spaces in data, including extra spaces between words
  • Removing range of characters
  • Extra space and different CASE in column header names
  • Combining multiple workbooks and worksheets dynamically
  • Comparing more than two tables
  • And many more….

Aside from imparting techniques to overcome these challenges, this course also covers common powerful and useful Power Query functions (M Functions) to solve problems when the Power Query interface falls short.

Please note that this is the second (Series 2) of two series courses for Excel Power Query:

  1. Excel Power Query Series 1 – Automating Data Preparation
  2. Excel Power Query Series 2 – Automate Complex Data Transformation

At the end of the course, learners will be able to:

  • Learn a better way connecting to a folder of files
  • Connect to a folder of files with different data structure
  • Reduce steps for better performance
  • Equip techniques for complex data transformation
  • Learn common powerful and useful M Functions to solve problems
  • Connect to SharePoint folder (without hands-on)
  1. For those who have attended our “Excel Power Query Series 1 – Automating Data Preparation” course.
  2. For existing regular Power Query users.

This is a hands-on practical course. Our trainer will walk you through each topic step-by-step. Those undergoing our Power Query training will also be provided with exercise files to practice the application methods at every stage. A short Q & A  session will be available after each topic.

Have attended our “Excel Power Query Series 1 – Automating Data Preparation” course or proficient in using Excel Power Query.

Below are the list of things to prepare for the Excel Power Query course:

  • Windows operating system laptop with Microsoft Excel version 2019 or higher or Office 365 with Power Query installed  (personal laptop is preferred as some company laptops may have security restriction)
  • Laptop power adaptor
  • USB mouse

Course Outline

  • A better way to connect to a folder of files
  • Connect to folder of files with different data structure
  • Connect to folder of files with different header names
  • Connect to folder of files with extra headers
  • Reducing steps for better performance
  • Combine two header rows into one
  • Index column within group
    • Messy to proper data structure
    • Replacing bulk values
    • Calculate difference between current & previous
    • Approximate match with Merge
    • Assigning unique ID for group
    • Advanced Unpivot techniques
    • Advanced Pivot techniques
    • Extract data from Form format
    • Self-reference query for incremental data loading
    • Create a custom Power Query Function
    • Dynamic Table column
    • Remove extra spaces including extra spaces between words
    • Remove range of characters from Field
    • Different Column header Case and extra spaces
    • Appending multiple Worksheets without Table
    • Reference Column names dynamically
    • Combine multiple Workbooks & Worksheets dynamically
    • Split dates and date range from a Cell into Rows
    • Compare multiple Tables using List Functions
    • Expenses distribution
  • Binary.Combine
  • Csv.Document
  • Duration.Days
  • Excel.Workbook
  • Folder.Files
  • List.ContainsAny
  • List.Dates
  • List.Difference
  • List.Intersect
  • List.PositionOf
  • List.Select
  • List.Transform
  • List.Union
  • Number.From
  • SharePoint.Files
  • Table.AddIndexColumn
  • Table.Column
  • Table.ColumnNames
  • Table.Combine
  • Table.ExpandTableColumn
  • Table.PromoteHeaders
  • Table.RemoveFirstN
  • Table.RenameColumns
  • Table.SelectRows
  • Table.ToColumns
  • Table.TransformColumns
  • Table.TransformColumnNames
  • Text.BetweenDelimiters
  • Text.Combine
  • Text.Length
  • Text.PositionOf
  • Text.Proper
  • Text.RemoveRange
  • Text.Split
  • Text.Trim
  • Connecting to SharePoint folder CSV / XLSX (Without Hands-On)

Our Dedicated Trainer

Having trained adult learners from both the public and private sectors, Alaster possesses a wealth of experience in IT training. In his pre-trainer years, Alaster has held many roles within the realm of software development, with a career that spans more than 12 years in the IT industry. Prior to being approved by People’s Association (PA), he was also an Associate Trainer within the Civil Service College. To date, he holds a Microsoft Office Specialist (MOS) certification and specialises in training Microsoft Office Applications and Adobe Photoshop.

With his vast knowledge and commitment to staying up to date with emerging trends, tools, and technologies within the IT and software industry, Alaster is extensively involved in developing course outlines and materials that bring value to learners, and equip them with up-and-coming skills which are in demand in the workforce.

Through implementation of innovative and effective methods of imparting knowledge, Alaster is able to engage his learners in courses he conducts. A large part of his training regime focuses on providing practical solutions based on real-life problems, especially in the Excel courses he conducts – such as the Power Query training course. As an affable and engaging trainer, Alaster is always willing to aid his learners in navigating problematic scenarios using the skills acquired, even after course completion. Well-known and liked for his dedication, commitment and passion for training, Alaster is very much appreciated by his learners.

This Excel Power Query – Automating Data Preparation is created and conducted by Mr Alaster Leong himself. Those seeking to expand their knowledge within the areas of automating their data preparation tasks using Power Query in Excel can sign up for this training session.

Course Registration

All Advanced Learning Singapore courses are not subsidised and non-claimable using SkillsFuture credits.

For organisations applying with more than 2 learners, please download and complete the registration form.

All registration forms should be submitted at least 2 weeks prior to course commencement. Many courses have pre-requisites, which are intended to foster a learner’s success in the course. Be sure to check out the necessary requirements before registering. Contact us for more information.

This course is not subsidised and non claimable using SkillsFuture credits.

Course Information

Course Code:

ALSIT-EPQ2

No laptop will be provided. Bring Your Own Laptop Course.

Course Fee:

S$860.00 (Nett)

S$731.00* (Nett)

Inclusive of lunch and 02 coffee breaks with snack items.

Call us for a group rebate when you register in a group of 8 or more.

Absentees will be charged the full course fees.

Course Dates:

2 days, 9:00am – 5:00pm
(14 hrs, exclude lunch break)

A closed class can be requested if you have a group of at least 6 or more registered for the same course and at a preferred month, subject to trainer availability.

Closed class is not customised class.

Training Venue:

To be confirmed one (1) week prior to the course start date.

Registration:

For Company-Sponsored learners: Click here to download registration form.

For Self-Sponsored learners: Click here to download registration form.

You can also register online here.

Note:

ALS reserves the right to reschedule, postpone or cancel any course, change the trainer and venue at short notice and at its absolute discretion, due to insufficient enrolment, class size, availability and circumstances outside our control.

More Original Courses Developed & Trained By Alaster