Power Query & Power Pivot

Learn Excel Power Query and Power Pivot Course Singapore

Singapore Excel Power Query and Power Pivot Objectives

Power Query and Power Pivot are two robust data analysis and manipulation features in Microsoft Excel, designed to streamline the process of importing, transforming, modeling, and analyzing data from various sources.

Power Query: is a versatile data transformation and cleansing tool that enables users to connect to a wide range of data sources, extract and transform data, and load it into Excel or other data visualization tools. With Power Query, users can effortlessly clean, reshape, and merge data from multiple sources, automating the data cleansing process. Its user-friendly interface allows for easy data previewing and editing before loading into Excel.

Power Pivot: is a powerful data modeling and analysis tool that empowers users to create complex data models and perform advanced calculations on large datasets. Power Pivot enables the combination of data from multiple sources, the creation of relationships between tables, and the execution of complex calculations using DAX (Data Analysis Expressions) formulas. Additionally, Power Pivot features a robust data visualization engine for creating interactive charts, graphs, and pivot tables.

Power Pivot is available with Microsoft 365 and products with a one-time purchase, such as Office Professional 2021, Office 2016 Professional Plus, and Excel 2016 Standalone.

Who Should Attend This Microsoft Excel Power Query and Power Pivot Course?

Students taking this course are experienced Excel users who are seeking to advance their data analysis capabilities by using Power Pivot and Power Query.

Pre-requisite for Excel Power Query and Power Pivot Course:

To ensure your success in this course, you should have experience working with Excel 2016 and PivotTables. You should already understand spreadsheet concepts and be comfortable creating and analyzing basic PivotTables, and PivotChart.

Kindly help us do this quiz to check your level before starting the training: Inspizone – Quick test

Upon completion of this 2-day training, participants will be able to use  Power Query and Power Pivot to perform powerful data analysis and create sophisticated data models.

• Day 1: how to use Power Query
• Day 2: how to use Power Pivot

Specially:

• Get started with the applicant.
• Visualize data.
• Work with advanced functionality.

Data Analytics with Excel Power Query and Power Pivot Training Course Outline

Day 1: Power Query Training Course Outline

Lesson 1: Introduction To Power Query

• Introduction
• Installing Power Query

Lesson 2: Data Loading From Sources

• Working with CSV files, Text Files and Excel Files
• Working with other file types

Lesson 3: Working With Folders And Multiple Files

• Connecting to a Folder of Files
• Combining Data from Multiple Files

Lesson 4: Filtering And Managing The Columns

• AutoFiltering
• Removing Duplicate Values
• Moving, Removing, Splitting, Merging Columns

Lesson 5: Working With Tools

• Text-Specific Tools
• Number-Specific Tools
• Date-Specific Tools
• Adding index

Lesson 6: Table Transformations

• Unpivoting Columns to Rows
• Pivoting the values
• Transposing a Table

Lesson 7: Modifying Queries

• Appending Queries
• Workbook Queries
• Merging Queries

Day 2: Power Pivot Training Course Outline

Lesson 1: Getting Started With Power Pivot

• Enable and Navigate Power Pivot
• Manage Data Relationships

Lesson 2: Visualizing Power Pivot Data

• Create a Power Pivot Report
• Create Calculations in Power Pivot

Lesson 3: Working With Advanced Functionality In Power Pivot

• Create a Key Performance Indicator
• Work with Dates and Time in Power Pivot

Appendix A: Commonly Used DAX Functions

• Unpivoting Columns to Rows
• Pivoting the values
• Transposing a Table

Course Fees:

•   For 2-day of training: S$395/pax + GST
•   For any one level only (Choose Day 1 Power Query or Day 2 Power Pivot): $245/pax + GST

*) New GST rate of 9% will be applied for all registrations made after 31st December 2023

Discount policy (Applied from 1 June 2023):

• 4 pax from a same group: 5% discount for each person
• 5 pax from a same group: 10% discount for each person

Fundings:

For Singaporeans and PR under company sponsorship: SDF claimable from Skills Development Funding $2/hour training -> Claimable amount is $14/pax/training-day.

For Individual self-sponsor:

Singaporean above 25: The course fee is claimable from Skillsfuture Credit (Mid-Career Transition NOT applicable)

• You can check your Skillsfuture Balance at Skillsfuture Website (Login with your Singpass)

NTUC union members (Only for participants taking 2-full-day physical class): Can claim from UTAP funding 50% of the unfunded course fee

• Unfunded course fee is the balance course fee payable after applying government subsidy.
• Enjoy up to $250 *unfunded course fee support when you sign up for courses supported under UTAP. NTUC members aged 40 and above enjoy higher funding support up to $500 each year, capped at 50% of *unfunded course fees, applicable for course starting before 31st March 2025.
• For more details, kindly visit: Union Training Assistance Programme (UTAP) (ntuc.org.sg)

Note: SkillsFuture Credit (SFC) cannot be used to co-fund the course fees of company-sponsored training courses.

Group & Corporate training

• From 10 participants joining together, we can open a new class on the requested schedule, subject to trainer availability (No group discount applied to this requested training)
• From 12 participants, we will be able to conduct in-house training with a customized course outline also.

Course Schedule

Two 15-min breaks in between; Lunchtime: 12.30-13.30 (Can be adjusted subject to the trainer & class pace)

Payment mode:

Bank transfer, cheque, paynow or nets accepted

Completion requirement:

A minimum of 75% attendance is required for completion of the course. 

Certificate

A Certificate Of Achievement will be issued upon completion of course