Advanced Learning Singapore

Download Year 2024 Training Calendar

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

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 beyond the Power Query interface.

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<

Upon completion 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 (demo only)

This Excel Power Query Series 2 – Automate Complex Data Transformation course is for:

This is a hands-on practical course. Trainer will walk through the topic step-by-step. Learners will be provided with exercise files on every topic to effectively apply what have been taught. A short Q & A session will be available after each topic.

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

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

Course Information


SGD 860.00 (Nett)

SGD 731.00* (Nett)
*Terms and Conditions apply.

Absentees will be charged the full course fees


9:00am to 5:00pm (14 hours)


Classroom @ Hotel

(Venue may be subject to changes)


15-16 Aug 2024
14-15 Oct 2024
16-17 Dec 2024

Why Learn With Us?

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
  • Merge & aggregation
  • Unstack data
  • 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
  • Create custom function for appending of data
  • Lookup & error handling
  • If then else, and/or and nested ifs
  • Binary.Combine
  • Csv.Document
  • Duration.Days
  • Excel.Workbook
  • Folder.Files
  • List.ContainsAny
  • List.Dates
  • List.Difference
  • List.FirstN
  • List.Intersect
  • List.PositionOf
  • List.Select
  • List.Split
  • List.Sum
  • List.Transform
  • List.Union
  • Number.From
  • Pdf.Tables
  • SharePoint.Files
  • Table.AddIndexColumn
  • Table.Column
  • Table.ColumnNames
  • Table.Combine
  • Table.ExpandTableColumn
  • Table.FromRows
  • 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 (Demo only)

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

Alaster has been training adult learners in IT in both the private and public sectors since 1986. Alaster’s career before training centred around IT for over 12 years, as he held various roles within software development. He has earned himself a Microsoft Office Specialist (MOS) certification and he is an existing Trainer approved by People’s Association (PA). He was also a former Associate Trainer with the Civil Service College.

He specialises on training Microsoft Applications and Adobe Photoshop. Based on his vast experience and keeping abreast with the emerging trends, tools and technologies with the IT industry, especially software, he has extensively involved in developing several course outlines and course materials and conducting training to equip learners with skills in-demand or likely to be most important in the jobs of the future work.

Alaster constantly explore innovative and effective ways in imparting knowledge and engaging his learners at the same time. He also focuses his training on providing practical solutions to real-life problems, especially Excel. He is more than willing to help overcome various problems faced by his learners that arise in their workplace in applying their newly-acquired knowledge and skills any time after the course. He is well-known for his dedication, commitment and passion to training that makes him well-liked and appreciated by his learners.

Share This Course

Scroll to Top