How do you clean data when you are building a dashboard?

Data Fluency Data Literacy Training
Avatar
Author

Cleaning Data for Dashboards

Business Case: Sales Performance Dashboard

Imagine you are a sales manager at a retail company. You’ve received a dataset containing sales data from various stores and would like to create a dashboard to monitor and analyze the sales performance of each store. However, before you can build the dashboard, you need to clean the data to ensure it is accurate and consistent. 

Step 1: Identify Data Issues

Once you’ve imported your dataset, start by reviewing the data and identifying any potential issues. Common problems include: 

  1. Missing or incomplete data 
  2. Duplicate records 
  3. Incorrect data types
  4. Inconsistent naming conventions

For our sales dataset, let’s say we’ve identified the following issues: 

  1. Missing values in the “Store Location” column 
  2. Duplicate sales records 
  3. The “Sales Amount” column is stored as text instead of a number 
  4. Inconsistent naming of products

 

Step 2: Fill in Missing Values 

To fill in missing values, first, determine an appropriate method for handling them. You can fill in missing values with a default value, use the mean or median, or interpolate based on surrounding data points. 

In our case, we will fill in missing “Store Location” values with “Unknown.” To do this: 

  1. Select the “Store Location” column. 
  2. Click on “Edit Column” and choose “Replace Missing Values.” 
  3. Select “Constant” and enter “Unknown” in the text box. 
  4. Click “Apply” to save the changes.

 

Step 3: Remove Duplicate Records 

Duplicate records can lead to misleading insights. To remove duplicates: 

  1. Click on “Data” or “Edit Data” in your dashboard software. 
  2. Locate the option to “Remove Duplicates” or “Deduplicate.” 
  3. Choose columns uniquely identifying a record (e.g., “Transaction ID”). 
  4. Click “Remove Duplicates” or “Apply” to remove the duplicate records.

 

Step 4: Correct Data Types 

Correct data types ensure that calculations and aggregations are accurate. To change the “Sales Amount” column to a number: 

  1. Select the “Sales Amount” column. 
  2. Click on “Edit Column” and choose “Change Data Type.” 
  3. Select “Number” or “Decimal” from the list of available data types. 
  4. Click “Apply” to save the changes.

 

Step 5: Standardize Naming Conventions 

Inconsistent naming conventions can lead to confusion and incorrect aggregations. To standardize product names: 

  1. Click on “Data” or “Edit Data” in your dashboard software. 
  2. Select the “Product Name” column. 
  3. Locate the option to “Find and Replace” or “Replace Values.” 
  4. Identify inconsistencies (e.g., “T-Shirt” vs. “Tshirt”) and replace them with a standardized value. 
  5. Repeat this process for all inconsistencies.

 

Helpful Tips and Tricks 

  1. Use the “Filter” or “Sort” options in your dashboard software to quickly identify data issues. 
  2. Regularly save your progress to avoid losing work due to unexpected software crashes or errors. 
  3. Make a backup of your original dataset before making changes, so you can always revert to the original data if needed. 
  4. Consider creating a data cleaning checklist to ensure you address all potential data issues. 
Related Tags: