WSQ - Excel Power Query and Power Pivot

Topic 1: Review Data with Power Query 

• Data types explained
• Query data from a table or range
• Query data from another Excel file
• Load data only as a connection
• Fill up and fill down
• Split column by delimiter
• Split into rows
• Add conditional and custom columns
• Add column by example
• Merge columns
• Sort and filter data in Power Query

Topic 2: Data Analysis with Power Query and Power Pivot 

• Use IF formulas
• Nest IF and AND
• AddDays to determine the deadline
• Pivot data in Power Query
• Pivot and append data
• Pivot and don't aggregate
• Unpivot data in Power Query
• Unpivot warnings
• Group By feature
• Appending Queries

Topic 3: Advanced Data Analysis using Power Query 

• Overview of joins in Power Query
• Walk through all six joins
• Joins: Left or right
• Outer join versus XLOOKUP
• Merge with multiple fields
• Approximate match equivalent of VLOOKUP: Binning
• Approximate match equivalent of VLOOKUP: Conditional column
• Cross Join
• Drill down to create a variable in Power Query
• Fuzzy matching by percentage
• Merging inconsistent data with a transformation table

Final Assessment 

• Written Assessment - Short Answer Questions (WA-SAQ)
• Practical Performance (PP)

Promotion Code

Promo or discount cannot be applied to WSQ courses

Minimum Entry Requirement

Knowledge and Skills

• Able to operate using computer functions
• Minimum 3 GCE ‘O’ Levels Passes including English or WPL Level 5 (Average of Reading, Listening, Speaking & Writing Scores)

Attitude

• Positive Learning Attitude
• Enthusiastic Learner

Experience

• Minimum of 1 year of working experience.

Target age group: 21-65 years old

Minimum Software/Hardware Requirement

Software:

You can download and install the following software:

• Microsoft 365 Defender

Hardware: Windows and Mac Laptops

About Progressive Wage Model (PWM)

The Progressive Wage Model (PWM) helps to increase wages of workers through upgrading skills and improving productivity. 

Employers must ensure that their Singapore citizen and PR workers meet the PWM training requirements of attaining at least 1 Workforce Skills Qualification (WSQ) Statement of Attainment, out of the list of approved WSQ training modules.

For more information on PWM, please visit MOM site.

Funding Eligility Criteria

Steps to Apply Skills Future Claim

• The staff will send you an invoice with the fee breakdown.
• Login to the MySkillsFuture portal, select the course you’re enrolling on and enter the course date and schedule.
• Enter the course fee payable by you (including GST) and enter the amount of credit to claim.
• Upload your invoice and click ‘Submit’

SkillsFuture Level-Up Program

The  SkillsFuture Level-Up Programme provides greater structural support for mid-career Singaporeans aged 40 years and above to pursue a substantive skills reboot and stay relevant in a changing economy. For more information, visit SkillsFuture Level-Up Programme

Get Additional Course Fee Support Up to $500 under UTAP

The Union Training Assistance Programme (UTAP) is a training benefit provided to NTUC Union Members with an objective of encouraging them to upgrade with skills training. It is provided to minimize the training cost. If you are a NTUC Union Member then you can get 50% funding (capped at $500 per year) under Union Training Assistance Programme (UTAP).

For more information visit NTUC U Portal – Union Training Assistance Program (UTAP)

Steps to Apply UTAP

• Log in to your U Portal account to submit your UTAP application upon completion of the course.

Note

• SSG subsidy is available for Singapore Citizens, Permanent Residents, and Corporates.
• All Singaporeans aged 25 and above can use their SkillsFuture Credit to pay. For more details, visit www.skillsfuture.gov.sg/credit
• An unfunded course fee can be claimed via SkillsFuture Credit or paid in cash.
• UTAP funding for NTUC Union Members is capped at $250 for 39 years and below and at $500 for 40 years and above.
• UTAP support amount will be paid to training provider first and claimed after end of class by learner.

• Data Analyst
• Business Analyst
• Financial Analyst
• Marketing Analyst
• Operations Analyst
• Business Intelligence Developer
• Report Developer
• Data Consultant
• Data Manager
• Risk Analyst
• Compliance Analyst
• Database Administrator
• Systems Analyst
• Performance Analyst
• Strategy Analyst
• Supply Chain Analyst
• Product Analyst
• Project Manager
• Research Analyst
• HR Analyst

Ken Hiong: Ken Hiong is a ACTA certified trainer. Ken has over 20 years of work experience in the healthcare and pharmaceutical industry, having assumed various functional and managerial roles in sales, marketing, business development, finance, business analysis and planning. With an interest and experience in scripting, Ken has worked on projects using HTML, CSS, PHP, MySQL, WordPress, MS Office, VBA, Power BI, etc. Notably, Ken is a proven expert Excel user at work who has made efficient many work processes, improved data analysis and enhanced the quality of business planning and reporting for organizations.

Ken graduated with a Pharmacy degree, holds a Master of Business Administration from the National University of Singapore and a Master of Applied Finance from the University of Adelaide. An ardent advocate of lifelong learning, Ken is ACTA trained and seeks to continually upgrade his IT skills through various channels of learning and Microsoft certifications. With MS Office Master qualification, Ken looks forward to assisting individuals and corporations improve their computer skills, productivity and business outcomes.

Liew Sing Loon: Liew Sing Loon is a ACTA certified trainer. Liew Sing Loon graduated from the National University of Singapore (NUS) in 1992 with a Second Upper Honors Degree in Electrical Engineering. He started his career as an Electronic Product Design Engineer for four years in a Multinational Company which sell the products worldwide. The job provided him practical knowledge in various aspects of manufacturing operations ranges from Design, Quality, Production and Procurement. He then spent fifteen years in Information Technology Sector as a Key Account Manager in selling IT solutions to Banking, Telecommunications and Government clients. The job provided him the opportunities to propose IT solutions, integrating software, hardware and services, to solve customers’ problems to achieve their strategic business objectives. He joint a German Product Test company to develop its business in providing product Safety, Immunity, Electromagnetic Compatibility (EMC) Certification test. While working in the industry, Liew Sing Loon obtained a Master of Science (MSc) in Technology Management from NUS.

In the last decade, Liew Sing Loon has been Adjunct Lecturer in several Institutes of high learning, transferring his knowledge on Quantitative & Statistical Analysis, Operations, Project & Quality Management to learners ranging from Diploma, full time undergraduates and working adults.Liew Sing Loon has a Certificate of Completion in Education Psychology Course and Teaching & Learning in Higher Education Program conducted by National Institute of Education (NIE).

Dr Alvin Ang: Dr Alvin Ang is a ACTA certified trainer. Alvin Ang did his Ph.D., Masters and Bachelors from NTU, Singapore. Previously he was a Principal Consultant (Data Science) as well as an Assistant Professor. He was also 8 years SUSS adjunct lecturer. His focus and interest is in the area of real world data science. Though an operational researcher by study, his passion for practical applications outweigh his academic background. He owns a startup externally

Dwight Nuwan Fonseka: Dwight Nuwan Fonseka is a ACLP certified trainer. He have a degree in Biotechnology from NUS, Advanced diploma in Pharmaceutical management from MDIS and Masters in Education from NTU. He have 8 years experience of teaching biology at O and A levels/ IB level in international schools in Singapore and overseas.