This full-day training programme is an intensive data cleaning and dashboard reporting course using Excel Power Query. Participants must have basic to intermediate level knowledge of Excel such as create Excel Table, create and format PivotTable, create and format 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 windows laptop, pre-installed with Microsoft Excel 2019, 2021, 365 (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 into the data model in a single Excel workbook. With Power Query, you can get data from different sources using a graphical interface and apply transformations using a Power Query Editor. Using Power Query, you can import data from many different of sources, clean it, transform it, then reshape it according to your needs. 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 not only allow you to save your data cleaning time, but also result in reduced manual errors.

Programme Outline

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.

• 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          
  • 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

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)