To reduce the environmental impact and contribute to sustainability efforts, ISCA will contribute our part by eliminating the printing of course materials for selected courses with effect from 2023.
Tips: To make your paperless learning experience more enjoyable, you may bring along a digital device such as a Windows based laptops or tablets to read your online materials during the class. QR code will be provided in the class for you to download the materials in PDF.
Join us and be a Difference Maker! | | 
|
|
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.
Competency Mapping
Category 5 = 14.00 Hours
Schedule & Fees
Date & Time
02 May 2024 (9:00 AM - 5:00 PM)
03 May 2024 (9:00 AM - 5:00 PM)
Registration is closed
Fee (inclusive of GST)
For Members:
$ 756.46
For Non-Members:
$ 898.16
Programme Facilitator(s)
Valene Ang
Venue
60 Cecil Street
ISCA House
Singapore 049709
Date & Time
26 Nov 2024 (9:00 AM - 5:00 PM)
27 Nov 2024 (9:00 AM - 5:00 PM)
Fee (inclusive of GST)
For Members:
$ 907.97
For Non-Members:
$ 1,078.01
Programme Facilitator(s)
Valene Ang
Venue
60 Cecil Street
ISCA House
Singapore 049709
Testimonial
Trainer was knowledgeable and helpful. The course was good in building my
understanding of powerquery and powerpivot
Past Participant
Funding
1] NTUC Union Training Assistance Programme (UTAP)
NTUC members enjoy 50% *unfunded course fee support for up to $250 each year when you sign up for courses supported under UTAP. NTUC members aged 40 and above can enjoy higher funding support up to $500 per individual each year, capped at 50% of unfunded course fees, for courses attended between 1 July 2020 to 31 December 2025.
*Unfunded course fee refers to the balance course fee payable after applicable government subsidies. This excludes material fees, registration fees, misc. fees etc.
This course is approved for UTAP support for intakes conducted between 08 February 2024 – 31 March 2025.
As UTAP is given on calendar year basis, and calculated based on year of training taken, it cannot be accumulated.
- Maintained paid-up NTUC membership before course, throughout course duration and at the point of claim and;
- Course by training provider must be supported under UTAP and training must commence within the supported period and;
- Unfunded course fee must not be fully sponsored by company or other types of funding
- Unfunded course fee must be S$20.00 and above, and;
- Member must achieve a minimum of 75% attendance for each application and sat for all prescribed examination(s), if any and;
- UTAP application must be made within 6 months after course ends.
For more information on UTAP Funding and to submit for UTAP claims, please visit https://www.ntuc.org.sg/uportal/programmes/union-training-assistance-programme. Terms and conditions apply.
Programme Facilitator(s)
Valene Ang
Valene Ang is a Microsoft Certified Trainer (MCT) and ACTA Certified trainer. She has 20 years training experience in Information Technology field. She had conducted many Microsoft Office seminars and workshops in Singapore and Malaysia. Her spreadsheet training focuses on providing practical solutions to problems in data analysis and reporting.
Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outline and courseware, assisting corporate client in business data analysis and providing dynamic report solutions.
To reduce the environmental impact and contribute to sustainability efforts, ISCA will contribute our part by eliminating the printing of course materials for selected courses with effect from 2023.
Tips: To make your paperless learning experience more enjoyable, you may bring along a digital device such as a Windows based laptops or tablets to read your online materials during the class. QR code will be provided in the class for you to download the materials in PDF.
Join us and be a Difference Maker! | |
|
|
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.
Competency Mapping
Category 5 = 14.00 Hours
Programme Facilitator(s)
Valene Ang
Valene Ang is a Microsoft Certified Trainer (MCT) and ACTA Certified trainer. She has 20 years training experience in Information Technology field. She had conducted many Microsoft Office seminars and workshops in Singapore and Malaysia. Her spreadsheet training focuses on providing practical solutions to problems in data analysis and reporting.
Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outline and courseware, assisting corporate client in business data analysis and providing dynamic report solutions.