This course is conducted using the Windows operating system. There will be no laptop provided. Kindly bring your own laptop.

2-Day Data Analysis with Excel Power Query, Data Model, DAX & Power Pivot Course in Singapore

Learn how Power Query can help you extract and transform data, and let Power Pivot and Data Analysis Expression (DAX) explore and analyse the data model for you.

All our courses are not subsidised and non-claimable using SkillsFuture credits

Data Analysis With Excel Power Query, Data Model, DAX and Power Pivot Course Overview

This course is created, owned, and trained by its original creator,  Mr. Alaster Leong. Learn from the original creator.

Important Note: 

Please check the following before registering:

  • This course is only for PC users with a copy of Microsoft Excel that is compatible with Power Pivot. Power Pivot is an add-in for the software, and not all versions can support this add-in. Learners must have one of these Excel versions.
  • Check which versions of Excel can support Power Pivot here, and learn how to turn on the Power Pivot add-in here. The guide to check Power Query availability here.

Your understanding of Excel for business may only be scratching the surface. Trying to use formulas to process data can be confusing and challenging, and there are many more functions that may look foreign. If you are looking for a way to game the system and elevate your Microsoft Excel proficiency to new heights for data analysis, this course is for you. Through a comprehensive training module, you will be introduced to Excel’s powerful data modelling and leverage specific business intelligence tools.

Excel’s Business Intelligence (BI) is a collection of tools and processes that are used to gather data and turn it into meaningful information to help users make better decisions. Within this depository, you will find Power Query, Power Pivot, and Data Analysis Expression (DAX) – dynamic tools to help you achieve greater efficiency with little effort.

What sets us apart from other training providers providing Power Pivot courses?

  • We do not have a readily created database for you. Since this is a project-based hands-on learning course, you will learn how to build data model from scratch from the raw data provided, clean up the data, build the data model with table relationships and use DAX formulas to generate other required data and KPIs, and display out as a Pivot Table final report.
  • You will learn the importance of Data Normalisation and how it works.

Learners of this course will undergo hands-on training with Power Query, designed to extract, cleanse, transform, and load data from flat files, folders, databases and more. You will practice shaping, blending, and exploring project files, and create completely automated loading procedures with just a few clicks.

Additionally, learners will also get the opportunity to explore data modelling, and learn the fundamentals of database design and normalisation (including table relationships, hierarchies and more). You will take a tour through Excel’s data model interface, and create your own relational database using our raw data provided for analysis throughout this course. The raw data provides in this course may not be applicable to your industry, but the knowledge and techniques learnt in the course are applicable to any industry data.

Finally, through the exploration and analysis of the data model, you will be able to grasp a clear understanding of Power Pivot and DAX. Unlike the traditional Pivot Table, Power Pivot allows you to comprehend hundreds and millions of rows across multiple data tables. It also creates powerful calculated fields and measures data using DAX – which is a formula language. Learners will get to learn basic DAX syntax, then be introduced to some of the most commonly used and powerful functions like CALCULATE, FILTER, SUMX, among others.

With the Excel Power Query, Data Model and Power Pivot course, you will be equipped with emerging data analysis skills to make your job more efficient. Becoming a powerful Excel user in Singapore is now easier than ever.

At the end of the course, learners will be able to:

  • Get themselves familiar with Excel’s data modelling and Business Intelligence
  • Use Power Query, Power Pivot and DAX to transform their workflow in Excel.

This course will benefit people who:

  • Are active Excel users who want to learn more advanced data modelling and Business Intelligence
  • Are looking to become power Excel users and enhance their analytics skill sets.
  • Are working in companies with more than 1,048,576 rows of data (maximum rows in Excel worksheet) and want to go beyond this limit.

This is a hands-on practical course. Our trainer will walk you through each topic step-by-step. Those undergoing our Power Pivot training course will also be provided with exercise files to practice the application methods at every stage. A short Q & A  session will be available after each topic.

To undergo our Power Query, Data Model and Power Pivot training course in Singapore, you will need to possess:

  • An installed Power Pivot add-in (refer to Important Note under Course Description)
  • Basic to intermediate Microsoft Excel skills
  • Knowledge on how to work with Excel functions
  • A thorough understanding of Excel Pivot Table (added advantage, but not mandatory)
  • Software Requirement: Microsoft Excel version 2019 or higher or Office 365 installed with Power Pivot turns on and Power Query available
  • PC/Windows operating system laptop with Microsoft Excel version 2019 or higher or Office 365 installed with Power Pivot turns on (personal laptop is preferred as some company laptops may have security restriction)
  • Laptop power adaptor
  • USB mouse

Course Outline

  • The Business Intelligence (BI) Components
  • Benefits of BI Tools
  • When to Use Power Query & Power Pivot
  • Types of Data Connections
  • The Query Editor & Tools
  • Data Loading Options
  • Basic Table Transformations
  • Using Various Data Types Specific Tools
  • Index & Conditional Columns
  • Pivot & Unpivot
  • Grouping & Aggregating Data
  • Working with Workbook Queries: Merging, Appending & Deleting
  • Connecting to a Folder of Files
  • Refreshing the Query
  • Starting Data Model
  • Changing to Different Views
  • Database Normalisation
  • Data Tables Lookup Tables
  • Primary & Foreign Keys
  • Working with Tables Relationship: Creating, Modifying & Deleting
  • Active Inactive Relationships
  • Relationship Cardinality
  • Filter Direction
  • Hiding Fields from Client Tools
  • Defining Hierarchies
  • Normal Pivot Table vs. Power Pivot
  • Introduction to Data Analysis Expressions (DAX)
  • Creating Power Pivot
  • Calculated Columns
  • Introduction to DAX Measures
  • Creating Implicit & Explicit Measures
  • Managing Measures
  • Calculated Columns vs. Measures
  • DAX Syntax
  • DAX Operators
  • Common DAX Function Categories
  • Maths & Stats Functions
  • Basic Logical Functions
  • Text Functions
  • Filter Functions
  • Iterator (“X”) Functions
  • Basic Date & Time Functions
  • Time Intelligence Functions (Optional)

Our Dedicated Trainer

Having trained adult learners from both the public and private sectors, Alaster possesses a wealth of experience in IT training. In his pre-trainer years, Alaster has held many roles within the realm of software development, with a career that spans more than 12 years in the IT industry. Prior to being approved by People’s Association (PA), he was also an Associate Trainer within the Civil Service College. To date, he holds a Microsoft Office Specialist (MOS) certification and specialises in training Microsoft Office Applications and Adobe Photoshop.

With his vast knowledge and commitment to staying up to date with emerging trends, tools, and technologies within the IT and software industry, Alaster is extensively involved in developing course outlines and materials that bring value to learners, and equip them with up-and-coming skills which are in demand in the workforce.

Through implementation of innovative and effective methods of imparting knowledge, Alaster is able to engage his learners in courses he conducts. A large part of his training regime focuses on providing practical solutions based on real-life problems, especially in the Excel courses he conducts – such as the Excel Power Query, Data Model and Power Pivot course. As an affable and engaging trainer, Alaster is always willing to aid his learners in navigating problematic scenarios using the skills acquired, even after course completion. Well-known and liked for his dedication, commitment and passion for training, Alaster is very much appreciated by his learners.

The Excel Power Query, Data Model and Power Pivot course is developed and conducted in Singapore by Alaster himself. Those seeking to expand their knowledge within the areas of data modelling and using BI tools effectively can sign up for the training sessions.

Course Registration

All Advanced Learning Singapore courses are not subsidised and non-claimable using SkillsFuture credits.

For organisations applying with more than 2 learners, please download and complete the registration form.

All registration forms should be submitted at least 2 weeks  prior to course commencement. Many courses have pre-requisites, which are intended to foster a learner’s success in the course. Be sure to check out the necessary requirements before registering. Contact us for more information.

This course is not subsidised and non claimable using SkillsFuture credits.

Course Information

Course Code:

ALSIT-PQP

No laptop will be provided. Bring Your Own Laptop Course.

Course Fee:

S$755.00 (Nett)

Inclusive of lunch and 02 coffee breaks with snack items.

Call us for a group rebate when you register in a group of 8 or more.

Absentees will be charged the full course fee.

Course Dates:

2 days, 9:00am – 5:00pm
(14 hrs, exclude lunch break)

A closed class can be requested if you have a group of at least 6 or more registered for the same course and at a preferred month, subject to trainer availability.

Closed class is not customised class.

Training Venue:

To be confirmed one (1) week prior to the course start date.

Registration:

For Company-Sponsored Learners: Click here to download registration form.

For Self-Sponsored Learners:
Click here to download registration form.

You can also register online here.

Note:

ALS reserves the right to reschedule, postpone or cancel any course, change the trainer and venue at short notice and at its absolute discretion, due to insufficient enrolment, class size, availability and circumstances outside our control.

More Original Courses Developed & Trained By Alaster