2-Day Excel Power Query Series 1 - Automating Data Preparation Course (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.

Power Query will change the way you use Excel.

Power Query is an Excel business intelligence tool that allows you to import data from a variety of sources to be cleaned, transformed and reshaped as needed (ETL process). Available in Excel version 2016 and above (Power Query is not available on Mac operating systems), it can be utilised to solve any challenging data analysis problems instantly. Unfortunately, many Excel users have yet to discover its existence or are simply unsure about its functions, and therefore continue to do things in the traditional “labour intensive” manner, or in the “complex” way using complex formulas.

Using Excel Power Query allows you to enjoy:

Quick processing and combination of data from different sources including Excel, text files, web data, online services, databases, and even documents stored in SharePoint folders.
Replacement of complex formulas with a few simple clicks
Efficient complex data transformation that usually requires a long time (e.g., unpivoting data).

Furthermore, your actions are recorded – so once you receive new data inputs, simply click ‘Refresh’!

And these are only just a few of many examples on how using Excel Power Query can improve your workflow and data analysis. Additionally, the knowledge and skills you learn through our Power Query training can also be applied to Power BI and Power Pivot.

Benefits of Excel Power Query

You may be wondering that these all sound great, but how exactly can you use this tool at work? Utilising Excel Power Query in your daily work can:

Change the way you interact with data in Excel and shave off hours which can be used to explore your data, develop powerful insights, and make informed decisions.
Effectively import and clean millions of rows of data into a data model for analysis.
Reduce the amount of work that goes into converting, cleaning, formatting, deleting and changing the formats of your source data. This saves the working time required to update the data analysis worksheet.
Easily create queries and data connections to multiple data sources and reports that will update when new data is added to the source.
Help to automate tedious and repetitive tasks without writing a single programming code.
Provide you with access to an array of powerful tools for transforming data all in one place.
Save hours of time that would have been spent manually copying and pasting via the traditional method.
Refresh your data quickly and easily with one click.
Reduce time spent on manual tasks.
Allow you to set up the query to be reused with a simple refresh.

You will find our Excel Power Query – Series 1 course a lot easier and fun than you may imagine. After your Power Query training, you will walk away with tools to solve real-world problems and make a positive impact in your work.

You might not know this, but in general, the more effort you put into learning something, the more rewarding it will get. However, with our Excel Power Query course, you will yield many rewards with just a little effort. Once again, this robust Excel tool will definitely blow you away with its data transformation capabilities.

Are you ready to undergo our in-depth Power Query training to learn a Smarter, Faster and Easier way to prepare your data? Take your Excel skills to the next level and change the way you interact with this software forever.

Course Information

Fee

SGD 860.00 (Nett)

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
(The hotel will be confirmed five days before the course start date)

Dates

10-11 Jul 2025
8-9 Sep 2025
10-11 Nov 2025

Important Note

Closed Class

Important Note

Closed Class

Why Learn With Us?

Objectives

Upon completion of the course, learners will be able to:

Import, clean, and transform large datasets from a variety of sources such as Excel, CSV and web data
Consolidate data from multiple Excel workbooks into one Table (or Pivot Table)
Combine data from all files within a folder (and make exceptions as needed)
Store the end result into Excel Data Model for further data analysis

Who Should Attend

This Excel Power Query course will benefit people who:

Receive daily, weekly, or monthly data files which require consolidation into one table for analysis. Once the next period’s file is received, they can then use Power Query to connect to the folder and automatically add the new data to the consolidated table
Often search for Excel formulas online and wonder if there is an alternative method.
Want to discover new ways to get work done faster with Excel
Work with big data using Excel
Are intrigued by the power dimension of Excel
Work with Excel reports, dashboards, and analysis, and spend countless hours preparing and cleaning up data from other Excel worksheets, financial systems, databases, websites, SharePoint, and other sources
Merge data using Vlookup or other lookup and reference functions
Are Excel users and are interested in automating the process of working with external data
Needs to combine data manually in Excel (with Power Query, you will no longer need to copy and paste)
Get their data from CSV, text or XML files, or other Excel
Are Excel users who work with data models

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

To undergo our Power Query training course, you will need to possess:

Intermediate Excel skills
A proficiency in Excel Pivot Table (an added advantage)
A laptop with Power Query available

Things To Bring

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

Course Outline

1. Introduction to Excel Power Query Course

What is Power Query?
Benefits of Power Query
The Process
Proper Data Set

2. Getting Started with Power Query

Analyse Large Data
Connecting to Source Data
Touring Power Query Editor
Changing the Source Path
Quick Insights on Data Quality & Distribution
Working with Applied Step
Changing Query Destination
Source Data Refresh Options

3. Power Query Tips & Tricks

Handling Changes to Source
Duplicate & Reference Query
Query Dependencies
Managing Query

4. Power Query Basic Transformation

Text Transformation
Sort & Remove Duplicates
Number Transformation
Working with Filter

5. Power Query Powerful Transformation

Column from Examples
Conditional Grouping Column
Conditional Column
Aggregating Data using Group By
Group By for All Rows
Unpivot Column
Pivot Column

6. Date & Time Transformation

Date Transformation
Creating Date from Text or Column
Time Transformation
Date & Number Error when Importing

7. Custom Column & Basic M Manipulation

Add Custom Column
Add Helper Step
Adjust Filter to Dynamically Reference to Helper Step
Create Dynamic Parameters using Drill Down

8. Power Query Online Data Sources

Importing Data from a Website
Importing Data from Google Sheets

9. Combine - Append Data

Append Data from Multiple Workbooks
Combine All Files from Folder (with Table)
Combine All Files from Folder (without Table)
Combine All Data Sheets from a Workbook
Combine All Data Sheets from Current Workbook

10. Merge - Join Kind

Merge – Left & Right Outer Join
Merge – Based on Multiple Columns
Merge – Inner, Full & Anti Joins
Merge – Fuzzy Match

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.