2-Day Data Analysis with Excel Power Query, Data Model, DAX and Power Pivot Course
Course Overview
This course is created, owned and trained by Mr. Alaster Leong. Learn from the original creator of this course.
Important Note:
Please check the following before registering:
- This course is only for Windows users with 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.
- To check which Excel versions can support Power Pivot, click here. To find out how to turn on the Power Pivot add-in, click here and the guide to check Power Query availability, click 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.
Course Information
Fee
SGD 755.00 (Net)
Absentees will be charged the full course fees
Time
9:00am to 5:00pm (14 hours)
Mode
Classroom @ Hotel
(Venue may be subject to changes)
Dates
09-10 Dec 2024
10-11 Feb 2025
10-11 Apr 2025
12-13 Jun 2025
11-12 Aug 2025
09-10 Oct 2025
08-09 Dec 2025
If you have a group of 6 or more individuals attending for the same course, you can opt for your own closed class on your preferred month, subject to trainer’s availability.
ALS reserves the right to reschedule, postpone or cancel any course, change the venue at short notice and at its absolute discretion, due to insufficient enrolment, class size, availability and circumstances outside our control.
Why Learn With Us?
Objectives
- Get yourself familiar with Excel’s data modelling and Business Intelligence
- Use Power Query, Power Pivot and DAX to transform your workflow in Excel.
Who Should Attend
This course will benefit people who are:
- Active Excel users who want to learn more advanced data modelling and Business Intelligence.
- Looking to become power Excel users and enhance their analytics skill sets.
- Working in companies with more than 1,048,576 rows of data (maximum rows in Excel worksheet) and want to go beyond this limit.
Methodology
This is a hands-on practical course. Trainer will walk through the topic step-by-step. Learners will be provided with exercise files on every topic to effectively apply what have been taught. A short Q & A session will be available after each topic.
Prerequisites
To undergo our Excel Power Query, Data Model and Power Pivot training course in Singapore, you will need to possess:
- Basic to intermediate Microsoft Excel skills
- Knowledge on how to work with Excel functions
- A thorough understanding of Excel Pivot Table (added advantage)
- A laptop with Power Pivot add-in installed (refer to Important Note under Course Description), Power Pivot turns on and Power Query available
Things To Bring
- A Windows operating system laptop with Microsoft Excel version 2019 or higher or Office 365 installed with Power Pivot turns on and Power Query available
- Laptop power adaptor and a USB mouse
Course Outline
1. Introduction to Excel Power Query, Data Model & Power Pivot Course
- The Business Intelligence (BI) Components
- Benefits of BI Tools
- When to Use Power Query & Power Pivot
2. Excel Power Query
- 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
3. Data Modeling
- 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
4. Excel Power Pivot
- 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
5. Common DAX Functions
- 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 Lead IT Trainer - Mr. Alaster Leong
This course is created, owned and trained by Mr. Alaster Leong. Learn from the original creator of this course.
Alaster has been training adult learners in IT in both the private and public sectors since 1986. Alaster’s career before training centred around IT for over 12 years, as he held various roles within software development. He has earned himself a Microsoft Office Specialist (MOS) certification and he is an existing Trainer approved by People’s Association (PA). He was also a former Associate Trainer with the Civil Service College.
He specialises on training Microsoft Applications and Adobe Photoshop. Based on his vast experience and keeping abreast with the emerging trends, tools and technologies with the IT industry, especially software, he has extensively involved in developing several course outlines and course materials and conducting training to equip learners with skills in-demand or likely to be most important in the jobs of the future work.
Alaster constantly explore innovative and effective ways in imparting knowledge and engaging his learners at the same time. He also focuses his training on providing practical solutions to real-life problems, especially Excel. He is more than willing to help overcome various problems faced by his learners that arise in their workplace in applying their newly-acquired knowledge and skills any time after the course. He is well-known for his dedication, commitment and passion to training that makes him well-liked and appreciated by his learners.
More Courses By Alaster
Data Analysis using Dynamic and Interactive Excel Dashboard Course
Data Analysis with Excel Power Query, Data Model and Power Pivot Course
Excel 100 Tips and Techniques Course (Exclusive)
Excel 365 Dynamic Arrays Series 1 – Power Functions Course (Exclusive)
Excel Advanced Formulas and Functions Course
Excel Automation with VBA Series 1 – Fundamentals Course
Excel Data Cleansing and Building Techniques Course
Excel Pivot Table In-Depth Course
Excel Power Query Series 1 – Automating Data Preparation (Exclusive)
Excel Power Query Series 2 – Automate Complex Data Transformation (New & Exclusive)
Power BI Desktop Series 1 – Data Model, DAX and Dashboard Visualization Course (Exclusive)
Power BI Desktop Series 2 – Advanced DAX Formulas Course (Exclusive)