Introduction to Microsoft Excel Power
Query, Data Model, Power Pivot & DAX
Nett Course Fee
$981
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
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
- Using the Power Pivot data model
- 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
- Creating your first calculated
- 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
Related Courses
Power BI Course – Bridging Big Data Analytics
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
Introduction to Microsoft Excel Power
Query, Data Model, Power Pivot & DAX
Course Duration:
2 Days (9.00am – 5.00pm)
Course Fee Details:
$981.00 (incl. of 9% GST)
SDF grant entitled, $2/hr x 14hrs = $28
Only applicable to Singaporean and PR employees
Skillsfuture Credit Claimable for Individual
UTAP Claimable
Course Code TGS-2020503210
Course Schedule
Course Name | Course Code | Venue | October 2024 | November 2024 | December 2024 | January 2025 | February 2025 | March 2025 |
---|---|---|---|---|---|---|---|---|
Introduction to Microsoft Excel Power Query, Data Model, Power Pivot and DAX | TGS-2020503210 | Hotel | 28-29 | 25-26 | 09-10 | 23-24 | 27-28 | 27-28 |