Introduction to Microsoft Excel Power
Query, Data Model, Power Pivot & DAX

Nett Course Fee
$981

Please be advised that as of 1st January 2025, there will be changes to both course fees and subsidies available.

Nett Course Fee (effective 1st January 2025)
$1090

Course Description

This course introduces Microsoft Excel’s powerful data modeling and business intelligence tools: Power Query, Power
Pivot, and Data Analysis Expressions (DAX).

Business intelligence (BI) is a collection of tools and processes that are used to gather data and turn it into meaningful information that people can use to make better decisions.

Prerequisites

To enrol in this course, you should possess the following:

(≥ 16 years old); or
1 year working experience

You should have basic to intermediate skills on MS Excel, know how to work with Excel functions and Pivot Table. Your laptop should be installed with Power Pivot.

Course Objectives

At the end of the 2 days training, you will be able to

• Use Power Query to import data into Excel through external sources such as Text files, CSV files, Web, or Excel workbooks whereby data can be cleaned and prepared for requirements.

• Understand how a Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel
workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables & PivotCharts.
• Use Power Pivot to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from
various sources, perform information analysis rapidly, and share insights easily.
• Use Data Analysis Expressions (DAX) to work with data models using formulas and expressions.

Course Outline

INTRODUCTION TO POWER QUERY

Installing and Activating a Power Query Add-In
Power Query Basics
Understanding Query Steps
Refreshing Power Query Data
Managing Existing Queries
Understanding Column-Level Actions
Understanding Table Actions
Importing Data from Different Sources
Managing Data Source Settings
Transforming Data
Other Useful Transformation
Understanding the Append Feature
Understanding the Merge Feature

Data Model

Understand Database Terminology
- Databases
- Tables
- Records, fields, and values
- Queries
- Relationships
- Power Pivot Internal Data Model
- Activating the Power Pivot Add-In

Power Pivot and Dax

Linking Excel tables to Power Pivot
- Using the Power Pivot data model
  in reporting
Loading data from Microsoft Access databases
Loading data from Text/CSV files
Getting data from folder
Understanding Power Pivot calculation
- Creating your first calculated
  column
- Formatting calculated columns
- Hiding calculated columns from end users
Utilizing DAX functions
- Referencing fields from other tables
Understanding Calculated Measures
- Creating a calculated measure
- Editing and deleting calculated
  measures
Using data with cube functions
Key Performance Indicators (KPIs)
- Create a KPI
- Edit a KPI
- Delete a KPI

This 1-day ‘Bridging Big Data Analytics Using Power BI’ course, endorsed by SkillsFuture Singapore (SSG), offers a solution to efficiently streamline data analysis and aid in proposal creation.

Course Duration:
1 Day (9.00am – 5.00pm)

View course details here

Visual Basic for Applications in Microsoft Excel – Fundamental

The ‘Visual Basic for Applications in Microsoft Excel – Fundamental’ course focuses on practical VBA programming skills to unlock spreadsheet potential, enhancing productivity through task automation and simplification, such as copying data between sheets or performing statistical functions across multiple sheets, addressing repetitive or intricate tasks effectively.

Course Duration:
1 Day (9.00am – 5.00pm)

View course details here