fbnoscript

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
  • 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)

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 NameCourse CodeVenueOctober 2024November 2024December 2024January 2025February 2025March 2025
Introduction to Microsoft Excel Power Query, Data Model, Power Pivot and DAXTGS-2020503210Hotel28-2925-2609-1023-2427-2827-28