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 data transformation skills to the next level, this course is for you!

In this hands-on course, you’ll learn new 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:

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

Objectives

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)

Who Should Attend

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

Those who have attended our “Excel Power Query Series 1 – Automating Data Preparation” course.
Existing regular Power Query users.

Methodology

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.

Prerequisites

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

Things To Bring

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

Fee

SGD 860.00 (Nett)

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

Absentees will be charged the full course fee

Includes lunch and two coffee breaks with light snacks, unless otherwise stated

Time

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

Mode

Classroom @ Hotel

Dates

17-18 Feb 2025
14-15 Apr 2025
16-17 Jun 2025
14-15 Aug 2025
13-14 Oct 2025
11-12 Dec 2025

Important Note

Closed Class

Important Note

Closed Class

Why Learn With Us?

Course Outline

1. Advanced Data Connection Techniques

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

2. Advanced Transformation Techniques

Reducing steps for better performance
Combine two header rows into one
Index column within group
Merge & aggregation
Unstack data

3. Complex Transformation Techniques

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

4. M Functions Taught in this Course

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

5. Optional Topics

Connecting to SharePoint folder CSV / XLSX (Demo only)

Our Dedicated Lead IT Trainer - Mr. Alaster Leong

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.