Data Training Courses

Home | Power BI | Excel | Python | SQL | Generative AI | Visualising Data | Analysing Data

How to clean messy data

Often, we receive a large dataset, possibly in an Excel spreadsheet. The dataset often has many columns. Here are some suggestions about how to check and clean the data.

Firstly inspect the data, and ask the question - is this reasonable?

What is the size of the table ? How many rows, columns?

Is the data really tabular?

1. Are the data in the rows consistent from row to row?
2. If there are any calculated columns, is the same calculation used for every row?
3. Does every cell have one and only one value?
4. Are there any ‘junk’ rows i.e. rows that do not represent an item of data e.g. subtotals, extra headers, comment lines, footers?
5. Is it two different types of data crammed into one table

Look at the data column by column and ask these questions:

• What is the data type of each column?
• Are the column names short and meaningful?
• Do date columns contain only dates?
• Do numeric columns only contain numbers?
• For categorical (text) columns, what is the distribution of the values, and the cardinality?
• For numerical columns, what is the min, max and average?

Are there any error cells? i.e. cells with #NA! or other errors?

Are there any missing values? If so:

• Is it reasonable to have missing values in this column?
• Do the missing values represent “not applicable” or “data not collected” or something else?

Is the data really tidy? Or is the same variable spread across several columns?

Are there any suspicious values, for example:

• possible mis-spellings
• outliers in numeric columns
• values that are much more frequent than we might reasonably expect?

Is the data denormalised - can we split it into a fact table with fewer columns and one or more dimension tables?

• see if columns with names that are likely to represent dimensional attributes e.g. NHS patient id, product, region,… There are often product classifications, people or entities (actors), geographical or date columns
• Are these value repeated? This would suggest that the column belongs in a dimension table

Act to clean the data

• shorten and rename the columns - move any long text to a column description metadata (many tools have this)
• remove any junk rows
• if this is Excel data, fix any #ERR! style errors
• move appropriate columns into a dimension table
• unpivot the data to go from wide to long (tidy) format.

Sometimes a dataset does not have a numeric column to be summed as the quantitative measure. Each row may represent for example an event or an employee and there may be no natural column to sum up. In this case, create a new measure that counts the number of items. for example in Power BI’s DAX language

Number of Items = COUNTROWS('Fact'[Fact Id])