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

Course Overview

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.

Mode of Assessment

No Assessment

Course Duration

2 Days

Total Training Duration (Hour)

16 Hours

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

Certification Obtained and Conferred by

Upon successful completion of the Introduction to Microsoft Excel Power Query, Data Model, Power Pivot & DAX course, participants who have attended at least 75% of the course will receive a Certificate of Attendance by @ASK Training.

Course Learning Outcome

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.

Pre-requisites

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.

Price

	Course Fee Payable
Original Fee	Before GST	With GST (9%)
Course Fee	$1,000.00	$1,090.00
Corporate Pricing (Fee payable to Training Provider)
SME	Before GST	With GST (9%)
SDF Funding	$1,000.00	$1,090.00
Non-SME	Before GST	With GST (9%)
SDF Funding	$1,000.00	$1,090.00