New user? Register as member

2-Day Microsoft Excel for Advanced Data Analysis: Leveraging Power Query, Data Model and Power Pivot

This 2-day hands-on workshop is designed for business professionals who want to streamline report preparation, improve data accuracy, and enhance their analytical capabilities using Microsoft Excel. Leveraging Power Query, Power Pivot, and DAX, participants will learn to build automated, scalable, and insightful reports — all without the need for macros or coding.

The course blends automation techniques with data modeling and introduces dynamic DAX formulas to help participants manage, analyze, and communicate data effectively in their business environment.

Objective

By the end of this workshop, participants will be able to:

• Automate repetitive data cleaning and consolidation tasks using Power Query.
• Build robust data models with Power Pivot and table relationships.
• Use DAX to create meaningful calculations and KPIs.
• Eliminate manual steps from monthly or weekly reporting.
• Design Excel dashboards that are both interactive and insightful.

Outline

Section 1: Excel BI Tools Introduction
• Overview: Power Query, Power Pivot, DAX
• When to use each tool
• Differences between Normal Pivot vs. Power Pivot

Section 2: Power Query for Data Automation
• Connecting to files, folders, and databases
• Data transformation: filtering, splitting, merging
• Creating conditional and index columns
• Pivoting, unpivoting, grouping data
• Merging & appending queries
• Refresh automation and folder-based queries

Section 3: Power Pivot Table Basics
• Classic vs. Power Pivot
• Loading Power Query data into the Data Model
• Creating basic Pivot Reports
• Inserting Slicers and Timelines

Section 4: Data Modeling Essentials
• Understanding Fact vs Lookup tables
• Creating and managing relationships
• Hiding fields and creating hierarchies

Section 5: DAX for Analysis
• Calculated Columns vs. Measures
• Writing basic and intermediate DAX formulas
• Logical functions: IF, SWITCH
• Time Intelligence (YTD, MTD, PYTD)

Section 6: End-to-End Automation
• Combine Power Query, Power Pivot, and DAX in a full reporting workflow
• Update reports in one click
• Common troubleshooting tips
• Best practices for Excel dashboards

Who should attend

This course is intended for:

• Finance Executives and Analysts
• Operations Managers and Planners
• Sales and Marketing Analysts
• Procurement and Supply Chain Professionals
• Anyone who prepares recurring reports or dashboards in Excel

Prerequisites:
Participants should have a good working knowledge of Excel formulas and experience using basic Pivot Tables.

Methodology

Two days of hands-on workshop

Profile of Jason Khoo

Jason Khoo is a Microsoft Certified Trainer (MCT), ACTA certified trainer of 20 years. He is a Microsoft Certified Power BI Data Analyst Associate and a data analytics practitioner of 30 years. During his time of employment, he has worked in Tibs (currently SMRT), MobileOne Ltd, 3M and Virgin Mobile.

During his tenure, he has worked with multiple databases, extracted and downloaded information from systems and run numerous analysis. These analyses spanned across many departments such as Finance, Sales, Marketing, Human Resources, Payroll, etc.

After his employment, he worked as a data analytics consultant for many companies, including Discovery Asia, 3M, Tanah Merah Country Club, Johnson and Johnson Vision Care, National Environmental Agency, Timberland, etc.
His data analytics skills combined with Excel allowed him to deliver many reports to the clients without them having to incur software cost. These reports include Dashboards, Top 10 charts, Risk Analysis, Business Models, KPIs, etc.

As a trainer, he is passionate about transferring his knowledge and imparting his skills to his participants. To day, he has trained thousands in his face to face workshops and had conducted talks for thousands as well.

Trans-Island Bus Services Ltd (Tibs) –Operations and Revenue Analytics, Passengers Travelling Behaviour Analytics
• Daily tracking of revenue,
• Prepare KPI for LTA,
• Fare revision analysis

Mobile One Asia Ltd (M1) – Financial analytics, Customer Call Usage Analytics
• Full company budgeting and business planning,
• Monthly Analysis,
• KPI reporting

3M – Sales, Cost and Inventory analytics
• Daily and Monthly Sales Forecasting,
• Analysis of Marketing Budget,
• Analysis of Inventory and shipments.
• Cost Analysis for new products (BOM analysis)

Virgin Mobile – Retail, Marketing, Payroll, Admin, IT, Finance Analytics
• Daily KPI reporting,
• Monthly Analysis for Finance, Payroll,
• Developed Fully Automated Business Model driven by KPI