Classroom/ Online: Yes/ Yes
Scheduling Date(s):
Note: Please click specific date for detailed venue and course fee etc.
Data Cleaning & Processing
Many people associate analytics with pretty charts, complex analyses leading to pivotal insights that support organization outcomes. But those are only some of the more visible aspects of analytics and the tip of the iceberg. There is much more to the analytics value chain; and it really starts with data. Data in its raw form may not be ready for analytics; data could be sitting across disparate locations, or may contain errors, or may lack crucial metrics that need to be calculated. A lot of works goes into preparing data for analytics; requiring unique skillsets quite different from analysis.
“It's amazing how much data is out there. The question is how do we put it in a form that's usable? – Bill Ford
About the workshop
This 2 Day workshop aims to provide a broad but practical overview of the analytics value chain, with a specific focus on data cleaning and processing. Delegates will get the opportunity to get their hands “dirty” with sizable data sets and experience what it takes to clean & process data across MS Excel and Power Query before turning them into accessible insights through interactive reports.
“It's amazing how much data is out there. The question is how do we put it in a form that's usable? – Bill Ford
About the workshop
This 2 Day workshop aims to provide a broad but practical overview of the analytics value chain, with a specific focus on data cleaning and processing. Delegates will get the opportunity to get their hands “dirty” with sizable data sets and experience what it takes to clean & process data across MS Excel and Power Query before turning them into accessible insights through interactive reports.
Objective
- Understanding of the analytics process, and the role of that Data cleaning & processing plays in the value chain
- Develop technical skills in data cleaning and processing, including merging data across multiple tables, managing various types of data errors & building new calculated data fields using Power Query and MS Excel interchangeably to support the process.
- Learn how to how to turn the processed data tables into interactive dashboard reports; as well as to automatically update the reports with new data.
Outline
Day 1
1. What is Analytics and what it takes to build sustainable capabilities
2. The Analytics value chain and the role of Data Cleaning and Processing
3. Assessing and validating data quality
4. Introduction to data cleaning and processing tools
5. Phase 1 - Restructuring and assembling master data tables
6. Phase 2 – Splitting data and fixing data errors
Day 2
1. Phase 2 (cont’d) - Splitting data and fixing data errors
2. Phase 3 – Building calculated data fields
3. Phase 4 – Interacting with the processed data table
4. Phase 5 – Building Interactive Reports based on the processed data table
5. Capstone Data exercise
1. What is Analytics and what it takes to build sustainable capabilities
- This segment seeks to define what analytics is and isn’t, why it matters in today world and what it takes to build sustainable value
2. The Analytics value chain and the role of Data Cleaning and Processing
- An introduction to the Analytics Value Chain; and appreciate the role thatData Cleaning and Processing plays in it.
- Data management refers to process of extracting, curating, transforming,and storing the data for analytics, which includes data cleaning & processing
3. Assessing and validating data quality
- Even with the right data files, it may not be ready for Analytics. Data needs to be reviewed and validated for accuracy and practicality before cleaning & processing
- Delegates will be exposed to the key considerations in assessing a data set; such as data definitions, data structure, identifying data matching keys, identifying data errors and possible calculated fields etc.
- Develop a data cleaning plan based on the assessment
4. Introduction to data cleaning and processing tools
- Introduction to MS Excel for data cleaning; key functions, capabilities, and limitations
- Introduction to Power Query as part of the data cleaning and processing toolkit across Microsoft; key differences, benefits and limitations compared to MS Excel
- Accessing and navigating PowerQuery
5. Phase 1 - Restructuring and assembling master data tables
- Data tables are not organized into the right structure for analytics; data fields may not be in the right data format and/or the requisite data fields may reside across multiple data tables
- This is a follow along hands-on exercise where students will learn the restructure data, reclassify data fields, and merge multiple data tables using Power Query
6. Phase 2 – Splitting data and fixing data errors
- Some data columns may contain more than one data field, which if not processed it could impact further processing
- Types of data errors – Missing data vs Zero data, consistent errors, inconsistent errors
- Fixing data errors using MS Excel – This is a follow along hands-on data exercises using a combination of IF, OR, AND, VLOOKUP and other Excel functions
Day 2
1. Phase 2 (cont’d) - Splitting data and fixing data errors
- Fixing data errors using Power Query – This is a follow along hands-on data exercises similar to the MS Excel example, but using Power Query; fixing errors through instruction, fixing errors by example
2. Phase 3 – Building calculated data fields
- The original set of data fields can only reveal so much; but when more data fields are combined, it opens up more possibilities to create new calculated fields that might reveal more insights
- Building calculated data fields with MS Excel – a follow along hands-on data exercise to create calculated data fields
- Building calculated data fields using Power Query - a follow along hands-on data exercise to create calculated data fields using Power Query
3. Phase 4 – Interacting with the processed data table
- Introduction to Pivot Tables to find answers hidden in the data table
- The thought process to finding answers using pivot tables
- A follow along hands-on data exercise to findings a series of questions using pivot tables
- Learn to Sort, re-order, summarize, group and visualize pivot tables
4. Phase 5 – Building Interactive Reports based on the processed data table
- Some of the data summaries may be used regularly, for reporting purposes. Rather than building them each time, pivot tables can be used to assemble these reports automatically. It may be further enhanced with interactivity, allowing different users to see summaries of different populations in the data.
- This is follow along hands-on data exercise to build the key skills to build interactive dashboards reports with MS Excel
- Delegates will also learn to update the database and interactive reports with new data
5. Capstone Data exercise
- Delegates will get the opportunity to experience what it takes to process raw data sets into clean and usable formats for analytics and generate meaningful and interactive reports from the processed data
- Delegates will apply the various frameworks, concepts and tools featured in
the workshop.
Who should attend
- Professionals with data management and/or reporting responsibilities; who are looking for a more structured and efficient approach to cleaning and processing data
- Analysts or Professionals looking to pick up new tips, tricks and tools to enhance their skills in data cleaning and processing
- All other professionals with a keen interest in developing strong foundations in data analytics
- Prior MS Excel training or experience is helpful, but not necessary
Methodology
A combination of lectures, discussions and interactions, as well as hands-on exercises, are designed to enable participants to acquire those practical skills. This workshop utilises MS Excel for all computer-based exercises.
Logistics Requirements
Participants are required to bring their own laptops with MS Excel (version 2013 or later) to participate in the data exercises
Workshops are designed to be delivered in Face to Face formats
Logistics Requirements
Participants are required to bring their own laptops with MS Excel (version 2013 or later) to participate in the data exercises
Workshops are designed to be delivered in Face to Face formats
Testimonials
“The trainer was patient in troubleshooting troubles faced by the participants, and it was useful being able to practice what was being taught.”
“Discovered that excel can do wonders! as a beginner to excel. The Trainer was very patient with me. 2 thumbs up!!!”
“A lot of practice given to us and this able to let us understand better.”
“FYTs data analytics workshop was practical and engaging. The trainer shared useful techniques, tips and shared relatable real-life examples. Right after the course, I was able to create an interactive dashboard to share with the team.”
“Discovered that excel can do wonders! as a beginner to excel. The Trainer was very patient with me. 2 thumbs up!!!”
“A lot of practice given to us and this able to let us understand better.”
“FYTs data analytics workshop was practical and engaging. The trainer shared useful techniques, tips and shared relatable real-life examples. Right after the course, I was able to create an interactive dashboard to share with the team.”
Particular of Michael-Lee
MBA, BSc
Principal Consultant
- An IT professional by training with more than 25 years of consulting & practical experience across many functions (sales, client management, business operations & customer service) throughout various roles (Vendor, Systems Integrator, Distributor and End-user) in the public & private sectors
- Prior to joining FYT, Michael managed the Asia Pacific Sales Operations for Linksys; his portfolio included the day-to-day operations within Asia Pacific. He was also running the China sales. Prior to that, Michael led the Asia Pacific Sales Operations for Cisco System and the Business Operations for Customer Service in Asia Pacific & Japan in Cisco. In addition, he headed a global Metrics & Business Intelligence team comprising members from Asia Pacific, Europe and the Americas.
- Michael holds a bachelor’s degree in Computer Science & Mathematics as well as an MBA from the National University of Singapore. Michael is also an ACTA certified trainer
