SOLUTIONS – Data Cleaning
Messy data should be cleaned up with Power Query because it turns a slow, error-prone chore into a fast, repeatable, and transparent process.
Manually fixing inconsistent formats, removing duplicates, or handling missing values in raw Excel sheets or CSV files takes hours and often introduces mistakes.
An an example, we need the data from a csv file in a proper format:
| Name, Age, Join_Date, Salary, Department, Email |
| john DOE, 32, jan 15 2020, $75,000, sales, john.doe@company |
| JANE SMITH, 29, 2021-03-22, 82000, marketing, jane.smith@company.com |
| bob jones, 45, 12/10/2019, 91,200, sales, bob.jones@company |
| ALICE Brown, , Feb 5 2022, 68000, SALES, alice.b@company |
| tom Wilson, 38, 2020.07.19, 74,500, Marketing, tom.w@company |
Issues:
- Inconsistent capitalization (names, departments)
- Missing age (Alice)
- Date formats mixed (jan 15 2020, 2021-03-22, 12/10/2019, 2020.07.19)
- Salary as string with $, ,, inconsistent numeric separators (91,200)
- Emails missing domain extension (john.doe@company)
- Extra spaces in some fields
With PowerQuery, we can clean and transform data into the proper format and then reuse the entire process in the future with just a click of a button once new data needs to be added.
| Name | Age | Join Date | Department | Salary | |
| John Doe | 32 | 15/1/2020 | Sales | 75.000 | john.doe@company.com |
| Jane Smith | 29 | 22/3/2021 | Marketing | 82.000 | jane.smith@company.com |
| Bob Jones | 45 | 12/10/2019 | Sales | 91.000 | bob.jones@company.com |
| Alice Brown | 5/2/2022 | Sales | 68.000 | alice.b@company.com | |
| Tom WIlson | 38 | 19/7/2020 | Marketing | 74.500 | tom.w@company.com |