New user? Register as member

Data Analysis with Excel Power Query, Data Model and Power Pivot

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.

You'll hands-on with Power Query; a tool that extract, cleanse, transform, and load data from flat files, folders, databases and more. You'll practice shaping, blending and exploring the project files, and create completely automated loading procedures with only a few clicks.

Next, you'll explore the Data Modeling and cover the fundamentals of database design and normalization (including table relationships, hierarchies and more). You'll take a tour through Excel's data model interface, and then create your own relational database to analyze throughout the course.

Next, you'll 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). You'll learn basic DAX syntax, then introduce some of the most commonly-used and powerful functions like CALCULATE, FILTER, SUMX and more.

IMPORTANT:
This course is only for PC users that have a copy of Excel that is compatible with Power Pivot. Power Pivot is an add-in for Excel. Not all Excel versions can support Power Pivot add-in. You must have one of these Excel versions. Click the first link below to check which versions of Excel can support Power Pivot. Then click the second link to learn how to turns on the Power Pivot add-in.

Link: http://bit.ly/WhereIsPowerPivot
Link: http://bit.ly/CheckPowerPivot

Objective

Outline

Section 1: Introduction
1. The Business Intelligence components
2. Benefits of Business Intelligence tools
3. When to use Power Query and Power Pivot

Section 2: Power Query
1. Types of data connections
2. The Query editor and tools
3. Data loading options
4. Basic table transformations
5. Using various data types specific tools
6. Index and conditional columns
7. Pivot and Unpivot
8. Grouping and aggregating data
9. Working with workbook queries; merging, appending, deleting
10. Connecting to a folder of files
11. Refreshing the query

Section 3: Data Modeling
1. Starting Data Model
2. Changing to different views
3. Database Normalization
4. Data tables vs Lookup tables
5. Primary and Foreign keys
6. Working with tables relationship; creating, modifying, deleting
7. Active vs Inactive relationships
8. Relationship Cardinality
9. Filter direction
10. Hiding fields from client tools
11. Defining hierarchies

Section 4: Power Pivot
1. Normal Pivot Table vs Power Pivot
2. Introduction to Data Analysis Expressions (DAX)
3. Creating Power Pivot
4. Calculated columns
5. Introduction to DAX Measures
6. Creating Implicit and Explicit Measures
7. Managing Measures
8. Calculated Columns vs Measures

Section 5: Common DAX Functions
1. DAX syntax
2. DAX operators
3. Common DAX function categories
4. Math & Stats functions
5. Basic Logical functions
6. Text functions
7. Filter functions
8. Iterator ("X") functions
9. Basic Date & Time functions
10. Time Intelligence functions (optional)

Who should attend

1. Excel users who want to learn more advanced data modeling & business intelligence tools
2. Anyone looking to become a power Excel user and enhance their analytics skillset
3. Company that have more than 1,048,576 rows of data (maximum rows in Excel worksheet) and want to go beyond the limits.

Prerequisite:
1. Must have Power Pivot installed (see IMPORTANT section above)
2. Must have basic to intermediate skills on MS Excel
3. Must know how to work with Excel functions
4. Must already know how to use Excel Pivot Table. If not, you can check out my course on "MS Excel Pivot Table In-Depth" at CCISG

Things to bring during the course:
1. A laptop (preferable your personal laptop)
2. Laptop power adaptor
3. A USB mouse
4. A USB thumb drive or hard-disk
5. Software requirement: Microsoft Excel version 2019 and above including Excel 365

Methodology

This is a two-days hands-on course. The trainer will walk through the topic step-by-step. You 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.

Testimonials

- Knowledgeable, able to answer questions and identify problems and thus resolve them
Nagase Singapore, Pte Ltd

- Practical tools that can be applied to data preparation
- Trainer is friendly and knowledgeable
Central Provident Fund Board

- Very useful workshop. The knowledge learned can be applied in my work. The workbook is very detailed and useful.

-  The trainer has provided a solution to my problems encountered in work.

Profile of Alaster Leong

Mr. Alaster Leong is an IT Trainer who possesses a strong passion in IT Training. He has more than 25 years of IT experience. He has been providing IT training in government, private institutions and organizations since 1992. He specializes in creative courses such as Adobe Photoshop, Adobe Dreamweaver, Adobe Flash, Adobe Acrobat, Access and Excel VBA, Microsoft Office Applications, etc. He is a certified Microsoft Office Specialist Expert (MOS) in Excel.