Update: I have created a playlist of suggested solutions to the dirty datasets provided in this article. I used Microsoft Power Query on Excel. To Learn more about Power Query, get this excellent book by the best Power Query resource in the world Power Query for Power BI and Excel.
The only guaranteed way to become better at cleaning dirty data is to avoid getting it. No, that is the biggest lie of the millennium. The opposite is the case. The only way to get better at preparing and cleaning dirty data is to clean a variety of them.
The problem, however, is to find a guaranteed source with lots of different dirty data cases for practice.
The objective of this article is to create a bank of different dirty data types, mostly simulated with real life scenarios of what I have encountered. I always say, data can be dirty in several millions of ways and you will never see it all.
I generally classify dirty data into 2 categories: Structure Dirty and Content Dirty. You should know there can be a third one, which is both dirty in structure and in content. A while ago, I wrote about how to clean dirty data, you should check it out to catch some tips before diving into this dirty ocean. The compilation I have here can be downloaded as Excel Files. Each workbook contains a worksheet for the raw dirty data and a second worksheet for a sample of the target solution.
Here you go:
1. Badly Structured Sales Data 1
Try re-arranging this data into the correct four columns. There has been a mix of rows and columns everywhere. Also, watch out for Grand Totals and Sub Totals, you do not need those in clean data.
Badly Structured Sales Data 1
2. Badly Structured Sales Data 2
This is pretty like number 1 above, with a different flavor. It has a date column and does not include totals.
Badly Structured Sales Data 2
3. Badly Structured Sales Data 3
Try re-arranging this data into the correct five columns. Again, you should watch out for Totals.
Badly Structured Sales Data 3
4. Badly Structured Sales Data 4
Very similar to number 3 above and with a little different flavor as well.
Badly Structured Sales Data 4
5. Jumbled Customer Details
We see this one often when you download or copy something from the web. You should separate the different data categories into separate columns.
Jumbled Customer Details
6. Medicine Data With Combined Quantity and Measure
Going by clean data rules, you should have every field/column represent unique things. So split the combined Quantity and Measure on this data into separate columns/fields. When you are done, your Quantity column should sum up to 17,600.00. You will find this total on the clean worksheet once you download.
Medicine Data With Combined Quantity and Measure
7. Hospital Data With Mixed Numbers and Characters
This data was collected by non-data-centric professionals. They have sometimes used letters in place of some numbers. Like using letter S in place of Number 5. When you are done with this, your numbers should sum up to that shown on the clean data from the download.
Hospital Data With Mixed Numbers and Characters
8. Invoices With Merged Categories and Merged Amounts
Because a single transaction (identified with an order id ) has multiple items purchased, who ever captured this data decided to create a single row for each order, thereby lumping the different items purchased and the amounts together into 2 fields respectively.
The better thing to do is to let each item purchased be on a single row with the amount. It is better to repeat the Order IDs on different rows than lumping up amounts in a single cell. We would be analyzing items bought and amounts a lot, we need them separated into rows.
Invoices With Merged Categories and Merged Amounts
If you have some samples, please do well to send them in to Info@foresightbi.com.ng and we’ll add them to the bank. Just send in a brief description of the data like I have done above. You can also include your social media links if you do not mind a proper mention.
Up your Data Hygiene Skills. Cheers.
Good afternoon Ahmed Oyelowo,
My name is Mercy Okere. I came across your blog today and I find it really interesting. Well done!
Do you have a step-by-step correction to the Badly Structured Sales Data 1?
Thank you.
Working on this soon. Should be available by 1st week of October with a free Intro to Power BI Course
You are doing great job, i really appreciate your work, the practical are amazing learnt a lot from it.
Hello, were you able to create the step by step correction?
Yes, it’s right here https://training.foresightbi.com.ng/courses/cleaning-dirty-data-samples
I am blown away at how detailed your tutorials are for these data cleanups! This is exactly what i needed, and you have also taught me how to clean data way faster with Power Query. I cannot thank you enough!
Thank you for you tutorials they have helped me so much! Using power query has helped me to clean even faster than before in Excel. I am blown away, this is awesome!
Can you please help as to understand the step by step guide how you have cleaned and structure the data
Thanks for your comment. You can check here for that https://training.foresightbi.com.ng/courses/cleaning-dirty-data-samples
Thank you for your data cleaning examples. They are clear, practical, and of an everyday type, immediately applicable.