ETL: Cleaning and Transforming Data

Description

This class introduces common techniques for cleaning and transforming data, including filtering, selecting, sorting, calculating, and summarizing information. These transformations are important because raw accounting data often need to be modified before they can answer a specific accounting or business question.

Motivation

How do we prepare messy data for analysis?

Review

The previous classes introduced the ETL process and showed how information from different sources can be combined. This class expands that process by considering how individual observations and variables can be changed, cleaned, and summarized to create useful analytical data.

Class plan

• This is the second of two classes focused on transforming data, we will start with a brief review of where we are in that process.
• There are two lab exercises in this class. First is the data cleaning task we started in class 04, the one with messy EmployeeCodes. Second, is another version of these cases where we will bring all of the pieces together (transforming/data cleansing, and joining, to find exceptions).
• At the end of this class, we will discuss automation approaches to solving these cases and demonstrate how to automate the workflow using PowerBI.

Materials

• Slides

  Class 05 Slides

  View slides: Class 05 Slides

Tools

• PowerBI
• Power Query

Data

Git extracted data in ACCTG320/data_cleansing (git pull maybe required).