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

Course Description

Introduction to Microsoft Excel Power Query, Data Model, Power Pivot and DAX is a highly recommended training course for individuals who want to get in-depth knowledge of handling significantly huge databases. This course introduces the most powerful data modelling and business intelligence tools. They are Power Query, Data Model, Power Pivot, and DAX (Data Analysis Expressions).

Why do we need Business intelligence (BI) tools?
A collection of tools and processes are required to gather data to turn it into meaningful information that people can use to make better decisions. Business Intelligence (BI) tools help in compiling and processing large groups of data and provide the required information for reporting, generating dashboards, and visuals.

Power Query
During the 2-day course, you will get hands-on with Power Query. Firstly, you will learn how to extract, cleanse, transform, and load data from flat files, folders, databases, and API services in the classes. Next, you will get the chance to practice shaping, blending, and exploring the project files. Thereafter, you will be confident to create automated loading procedures with just a few clicks.

Data Modelling & DAX
While moving forward in the course, we will be guiding you to dive into Data Modelling which focuses on databases relationships, cardinality, and hierarchies. We will show you how to explore Power Pivot and DAX to analyse the data model. Lastly, you will also learn the most powerful and commonly used functions such as – CALCULATE, FILTER, SUMX and many more.

Course Objectives

  • Have hands-on experience with Power Query
  • Explore the Data Modeling and cover the fundamentals of database design and normalization (including table relationships, hierarchies and more)
  • Use Power Pivot and DAX to explore and analyze the data model. Unlike traditional Pivot Table, Power Pivot allows you to analyze hundreds of millions of rows across multiple data tables, and create powerful calculated fields and measures using a formula language called Data Analysis Expressions (or “DAX” for short)

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

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)

SkillsFuture Credits & UTAP claimable

SDF grant entitled, $2/hr x 14hrs = $28
Eligible for Singaporeans, and PR employees

Course Code TGS-2020503210

Course Schedule

Course NameCourse CodeVenueApril 2024May 2024June 2024July 2024August 2024September 2024
Introduction to Microsoft Excel Power Query, Data Model, Power Pivot and DAXTGS-2020503210Hotel29-3027-2824-2529-3026-2723-24

Nett Course Fee