New user? Register as member

Data Analysis and Transformation with Excel 365 Power Pivot and Power Query

Data transformation with Power Query involves a series of steps that allows users to clean, shape, and prepare data for analysis and reporting within the Power Query Editor. Power Query records all transformations as "Applied Steps," visible in the Query Settings pane. Users can review, modify, or delete individual steps to adjust the transformation process.

Data analysis with Power Pivot involves using the Power Pivot add-in for Excel to create sophisticated data models from large datasets, combine multiple data sources, and perform advanced calculations using the Data Analysis Expressions (DAX) formula language to build interactive PivotTable reports and dashboards. It handles millions of rows, establishes relationships between tables, and generates Key Performance Indicators (KPIs) for in-depth performance analysis.

Participants should bring a laptop with Microsoft Office 365.
Must have Power Pivot installed
This course is not for Mac version Microsoft Excel.

Objective

Outline

Chapter 1: Introducing Power Query and Power Pivot

1. When to use Power Query and Power Pivot
2. Exploring Power Query Editor
3. Data Types in Power Query
4. Installing Power Pivot Add-In
5. Exploring Power Pivot Window
6. Normal Pivot Table vs Power Pivot

Chapter 2: Getting Data into Power Query

1. Creating New Query from Table
2. Defining Column Data Types
3. Loading Query Results on New Worksheet
4. Loading Query Results on Existing Worksheet
5. Importing Data from CSV File
6. Importing Data from Excel Workbook
7. Loading Query Results as PivotTable
8. Loading Query Results as PivotChart
9. Creating Data Connections Only
10. Combining Files from A Folder
11. Refreshing The Query Results

Chapter 3: Adding Calculated Columns

1. Performing Statistical Operations
2. Performing Standard Math Operations
3. Performing Rounding on Numbers
4. Performing Date & Time Calculations
5. Adding Custom Column
6. Adding Conditional Column

Chapter 4: Creating and Combining Multiple Queries

1. Creating Duplicate Query
2. Creating Reference Query
3. Combining Tables with Append Query
4. Combining Tables with Merge Query
5. Combining Tables in a Workbook

Chapter 5: Transforming Data

1. Removing Duplicate Rows
2. Filtering and Sorting Rows
3. Splitting Selected Column
4. Grouping Rows Based on Selected Columns
5. Replacing Values
6. Changing Text Case
7. Trim and Clean Text
8. Adding Prefix and Subfix
9. Extracting Characters
10. Merging Selected Columns
11. Transposing Table
12. Filling Missing Data
13. Unpivot Other Columns
14. Adding Column from Examples

Chapter 6: Preparing Data for Power Pivot

1. Understanding Database Normalization
2. Defining Primary and Foreign Key
3. Loading Data into Power Query Editor
4. Removing Top Rows
5. Removing Duplicate Rows
6. Trimming Additional Spaces
7. Filtering Selected Rows

Chapter 7: Working with Power Pivot

1. Loading Data from Power Query
2. Formatting Data in Data View
3. Adding Excel Table to Data Model
4. Creating Table Relationships in Diagram View
5. Creating Power PivotTable
6. Importing Data from Text File
7. Importing Data from Excel File
8. Creating Power PivotChart
9. Importing Data from Microsoft Access

Chapter 8: Loading Data into Power Pivot

1. Adding Calculated Columns with Formulas
2. Introduction to Data Analysis Expressions (DAX)
3. Adding Calculated Columns with DAX Functions
4. Calculated Columns vs Measures
5. Creating Power PivotTable using Measures
6. Creating Implicit and Explicit Measures
7. Managing Measures
8. Creating and Managing KPI
9. Creating Power PivotTable using KPI

Chapter 9: Creating Power PivotTable and PivotChart

1. Creating PivotChart and PivotTable
2. Formatting PivotChart and PivotTable
3. Adding Slicer to PivotTable
4. Connecting Slicer to Chart and Table
5. Adding Timeline to PivotTable
6. Connecting Timeline to Chart and Table
7. Introduction to Hierarchies
8. Creating and Managing Hierarchies
9. Hiding fields from client tools
10. Creating PivotTable using Hierarchies
11. Using Drill Up and Drill Down

Chapter 10: Creating Power Pivot Dashboard

1. Introduction to Dashboard Report
2. Creating Dashboard with Four Charts Layout
3. Applying Top 10 Filter to PivotChart Values
4. Sorting PivotChart Values
5. Adding Slicer and Timeline to PivotChart
6. Connecting Slicer and Timeline to PivotChart
7. Formatting the Dashboard
8. Protecting the Dashboard

Who should attend

Methodology

Lecture style, with hands-on exercises

Profile of Valene Ang

Valene Ang is a Microsoft Certified Trainer (MCT) with a degree in Business Computing. Her Professional qualifications including Advanced Certificate in Training and Assessment (ACTA) and Master Instructor for Microsoft Office Specialist (MOS). She has broad experience in corporate IT training and course materials development.

Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outlines and course materials, assisting corporate clients in business data analysis and providing dynamic report solutions. Her training focuses on providing practical solutions to real life Excel problems.

Valene conducted many Microsoft Office training in Singapore, Malaysia and China. Her corporate clients include NOL, PSA, IRAS, DFS, CPF, PUB, MOM, MOE, NEA, DHL, SingTel, Singapore Expo, Changi Airport Group, SPRING Singapore, Nanyang Polytechnic, Singapore Polytechnic, Republic Polytechnic, Denza (ShenZhen) and etc..