Data Wrangling and Preparation with Excel

Programme Outline

Learning Objectives and Structure

1. Perform the data preparation component for the role of a junior data scientist or a data engineer or a business analyst role.
2. Understand the types of data and databases in the business context
3. Appreciate the use of data dictionary and harness the potential of metadata for data science
4. Acquire organizational dataset from data lakes and other democratized data sources for data enrichment purposes
5. Structure data into an appropriate form for data analysis
6. Manipulate data structures to support data-wrangling phase
7. Perform data wrangling on the acquired dataset
8. Address data quality issues with appropriate data cleansing technique
9. Iterate the data mining process progressively with the provision of data wrangling and exploratory analysis tools

Programme Structure: Participants will go through 4 days of training. Class will reconvene on the 5th day for a presentation as part of the course assessment.

Day 1

• Overview of Data Science Pipeline
• What is Data Wrangling and Data Preparation?
• Data acquisition
• Understand how data scientist prepares the dataset for data modelling
• What is data discovery?
• Types of Data
• Types of Databases
• What is a Data Dictionary and Metadata
• Data Models

Day 2

• Data Preparation Phase
• Pivoting Large Dataset
• Power Pivot
• Structural Dataset Querying with Excel Lookup and Reference Functions
• Structural Dataset Filters

Day 3

• Power Query
• Exploratory Data Analysis with Charts
• Power BI

Day 4

• Dataset Cleaning with Conditional Formatting
• Dataset Control Structure with Control Functions
• Data Quality Control with Logical, Booleans and Information Functions
• Dataset Aggregation with Excel Power Functions
• Handling Unstructured Data

Day 5

• Project Presentation

Assessment

Participants will be assessed via group based project presentation on the 5th session of the course. There will also be formative assessment and case studies to assess a participant’s understanding and competency.

Subject Credits

Upon completion and satisfying the requirements of passing this course, learners will be awarded 12 subject credits.