This two-day training programme is an intensive data cleaning, data analysis and dashboard reporting course using Excel Power Query and Power Pivot. Participants must have basic to intermediate level knowledge of Excel such as  create Excel Table, create calculated columns in Excel Table, create table relationship, create and format PivotTable, perform calculations in PivotTable, create Calculated Field in PivotTable, create and format PivotChart, create slicer driven PivotChart as a prerequisite. Participants are encouraged to attend IT031C to obtain the prerequisite knowledge.

Software Installation and Laptop

As this class places strong emphasis on the hands-on experience, you will be working with data for the majority of the time in the class. To optimize the learning experience, it is mandatory for all participants to be equipped with a laptop, pre-installed with Microsoft Excel 2010, 2013, 2016 (full licence) or any latest version.

Programme Objective

Power Query is a business intelligence tool available in Excel that allows you to import millions of rows from many different of sources into the data model in a single Excel workbook, clean it, transform it, then reshape it according to your needs. With Power Query, you can get data from different sources using a graphical interface and apply transformations using a Power Query Editor. In this way, you can set up a query only once (all steps can be saved) and then re-use it later by simply refreshing it every time your data changes. This is not only allowed you to save your data cleaning time, but also will result in reduced manual errors.

Power Pivot allow you perform powerful data analysis and create sophisticated data models. It transforms an analyst’s ability to work with large amounts of related data, eliminating intensive lookup formulas and giving them a way to produce fast, repeatable analysis. With Power Pivot, you can import millions of records from multiple data sources into a single Excel workbook, create calculated columns using Data Analysis Expressions (DAX) functions, create Data Model, create Key Performance Indicator (KPIs) to track performance against targets and create calculated measures that aggregate data from different rows on a PivotTable.

Programme Outline

• Understanding Power Query        
  • Overview of Excel Power Query   
  • When To Use Power Query?
  • Power Query Editor Ribbons
  • Data Sources and Data Types
  • Explore Data Source Settings
  • Explore Query Settings
• Cleaning Data with Power Query
  • Get Data from Excel Table
  • Change Data Types
  • Choose Columns
  • Go To Column
  • Remove Columns
  • Remove Duplicate Rows
  • Replace Values
  • Fix Specific Formatting
  • Sort and Filter Data
  • Load Query Results into Excel Table
• Transforming Data with Power Query    
  • Import Data from Excel Workbook
  • Group Data By Column
  • Split Column By Delimiter into Columns
  • Split Column By Delimiter into Rows
  • Format Text Column
  • Extract Characters from Text
  • Merge Columns into One Column
  • Fill In Missing Data
  • Transpose Table
  • Unpivot Columns
• Calculating with Power Query     
  • Extract Characters to New Column
  • Merge Columns into a New Column
  • Perform Math Operations
  • Calculate Age From DOB
  • Calculate No Of Days Between Two Dates
  • Extract Quarter from Dates
  • Extract Hour from Time
  • Create Column from Examples
  • Create Custom Column
  • Create Conditional Column
• Combining Files with Power Query
  • Combine Files from A Folder
  • Combine Files with Append Query
  • Merge Query with Another Query
  • Duplicate a Query
  • Reference a Query
• Creating HR Dashboard     
  • Build a Dashboard to show total Employees and new employees by years, quarters and months, total employees by Region, total employees by gender and ethnic, total employees by age group and gender, employee turnover rate by gender.
  • Using Power Query to import HR data
  • Calculate age of employees
  • Create PivotChart from Data Model
  • Format PivotChart using new Charting Tools
  • Add Slicers to the Dashboard
• Understanding Power Pivot          
  • Overview of Excel Power Pivot    
  • Benefits and Drawbacks of Using Power Pivot  
  • The Different between Normal Pivot and Power Pivot  
  • The Power Pivot Window  
  • Data Sources and Data Types       
  • Power Pivot Internal Data Model 
  • Primary Key and Foreign Key Field          
  • Table Relationships 
  • Creating Relationships in Diagram View  
• Getting Data into Power Pivot     
  • Adding Excel Tables to Data Model         
  • Managing Relationships     
  • Creating PivotTable Using Data Model    
  • Importing Access Database
  • Importing Data from Other Sources         
  • Changing Existing Data Source in Power Pivot  
  • Refreshing Data in Power Pivot    
  • Freezing Columns in Power Pivot 
• Creating Power PivotChart and PivotTable       
  • Creating Power Pivot Charts         
  • Creating PivotTable and Using Hierarchies         
  • Creating Combo Chart        
  • Enhancing PivotTable Visualization          
  • Filtering Data in Power Pivot        
  • Sorting A Column By Other Column        
•  Creating Calculations in Power Pivot     
  • Types of Calculations in Power Pivot       
  • Overview of Calculated Column and Field          
  • Creating Calculated Columns using Formulas     
  • Creating Calculated Columns using DAX Functions           
  • Creating Calculated Column using Fields from another Table           
  • Creating Measure (Calculated Field)         
  • Creating Measures using DAX Functions
  • Creating Key Performance Indicator (KPI)           
•  Building Power Pivot Dashboard
  • Understanding Dashboard 
  • Importing Source Data into Power Pivot
  • Creating Table Relationships
  • Creating Power Pivot Charts         
  • Formatting Pivot Charts     
  • Adding Slicer and Timeline
  • Create New Slicer Style

Training Methodology

Lecture style, with computer hands-on exercises

Closing Date for Registration

1 week before programme or until full enrolment.

Intended For

• If you need to load data from multiple sources, clean up data, create calculated columns, shape and transform massive data in ease, this is the right course for you.
• Participants should be proficient in Excel features such as PivotTable, PivotChart, slicer, filter and functions in order to benefit most from the course.
• This course may not be suitable for beginners who know some basic features of Excel and seldom use Excel program at work.

Programme Facilitator(s)