2-Day Excel Power Query Series 1 - Automating Data Preparation Course
In Singapore (Exclusive)
This Excel Power Query training helps you transform data in ways you never thought were possible.
All our courses are not subsidised and non-claimable using SkillsFuture credits
Excel Power Query Series 1 - Automating Data Preparation Course Overview
Course Description
Learn from the original creator of this course – Mr. Alaster Leong.
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 Power Query training? Take your Excel skills to the next level and change the way you interact with this software forever.
Course Objectives
At the end 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 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. 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.
Pre-requisites
To undergo our Power Query training course, you will need to possess:
- Intermediate Excel skills
- A proficiency in Excel Pivot Table (an added advantage)
- Software Requirement: Microsoft Excel version 2019 or higher or Office 365 installed with Power Query available
Things To Bring
Below are the list of things to prepare for the Excel Power Query course:
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 Trainer
Mr Alaster Leong
Learn from the original creator of this course.
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-EPQ1
No laptop will be provided. Bring Your Own Laptop Course.
Course Fee:
S$860.00 (Nett)
Call us for a group rebate when you register in a group of 8 or more.
Absentees will be charged the full course fee.
Course Dates:
- 02 - 03 Nov 2023
- 11 - 12 Jan 2024
- 04 - 05 Mar 2024
- 16 - 17 May 2024
- 15 - 16 Jul 2024
- 09 - 10 Sep 2024
- 14 - 15 Nov 2024
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 date at a preferred date, 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
Data Analysis Using Dynamic And Interactive Excel Dashboard Course
Data Analysis With Excel Power Query, Data Model And Power Pivot Course
Design And Build Digital Word Fill-In Form And Auto Export Collected Data to Excel Course
Excel 100 Tips And Techniques Course (Exclusive)
Excel 365 Dynamic Arrays Series 1 – Power Functions (Exclusive)
Excel Advanced Formulas And Functions Course
Excel Automation With VBA Series 1 – Fundamentals Course
Excel Data Cleansing And Building Techniques Course
Excel Pivot Table In-Depth Course
Power BI Desktop Series 1 – Data Model, DAX And Dashboard Visualization Course (Exclusive)
Power BI Desktop Series 2 – Advanced DAX Formula (Exclusive)