How to clean data in Excel before analyzing it: the eight most common problems and their Power Query fix
Most of an analysis's time does not go into analyzing but into cleaning: duplicates, invisible spaces, dates that are text, the same category written four ways. This guide walks through the eight problems we find in almost every file from companies and agencies, with the Power Query fix that is applied once and repeats itself every month.
In this article
- Why clean in Power Query and not by hand
- 1. Duplicates
- 2. Spaces and invisible characters
- 3. Dates that are text
- 4. Inconsistent categories
- 5. Numbers that are text
- 6. Merged cells and multi-row headers
- 7. Data in "wide" format (one month per column)
- 8. Several files with the same format
- Checklist before analyzing
- Want to bring this to your team?
Why clean in Power Query and not by hand
Cleaning by hand works once. Next month the same problems arrive and it has to be repeated, and nobody remembers what was corrected. Power Query records every cleaning step: next month a refresh is enough. It also separates the source (untouched) from the clean result, so you can always go back.
1. Duplicates
Repeated rows from copying a file twice or duplicate records in the system. In Power Query: select the columns that identify a record (for example, folio and date) and "Remove duplicates". Before removing them, it pays to count them: if they are 20% of the rows, the problem is at the source.
2. Spaces and invisible characters
"García " with a trailing space or line breaks inside a cell break matches and groupings. In Power Query: Transform → Format → Trim and Clean on text columns. It is the step that solves the most "impossible" matches.
3. Dates that are text
"03/04/2026" can be April 3 or March 4, and if it is text it neither sorts nor groups by month. In Power Query: change the type to Date using the right locale (Change type → Using locale → Spanish (Mexico)). If several formats are mixed, a conditional column separates them first.
4. Inconsistent categories
"CDMX", "cdmx", "Cd. de México", "Ciudad de Mexico". In Power Query: Transform → Format → Uppercase or Capitalize each word to unify case, then Replace values (or merge with an equivalence table) for the variants. The definitive fix is data validation at the source (we explain how here).
5. Numbers that are text
Figures with "$", thousands separators or spaces import as text and do not add up. In Power Query: Replace "$" and "," with nothing and change the type to Decimal number. If the decimal separator is a comma, use the right locale when changing the type.
6. Merged cells and multi-row headers
"Pretty" reports made for printing have merged titles and two-row headers; that is not a table. In Power Query: Remove top rows until the real header, Use first row as headers and Fill down the columns that came merged.
7. Data in "wide" format (one month per column)
January, February, March as columns does not analyze well: every new month changes the structure. In Power Query: select the month columns and "Unpivot" to get two columns: Month and Value. It is the step that confuses most at first and delivers most later.
8. Several files with the same format
Twelve branch files or fifty agency files. In Power Query: Get data → From folder, Combine files and apply the cleaning steps once to all of them. The full case is in Power Query vs pivot tables.
Checklist before analyzing
| Question | If the answer is no… |
|---|---|
| Is each row a record and each column a variable? | Unpivot / fill merged cells |
| Are dates of type Date? | Change type with locale |
| Are numbers of type Number? | Replace symbols and change type |
| Is each category written one way only? | Unify case and replace values |
| No duplicates by key? | Remove duplicates by key columns |
| Any spaces or line breaks? | Trim and clean |
With that list all green, the analysis truly starts: pivot tables, indicators and dashboard. It is the second module of the data analysis with Excel course and one of the most valued by people who come from "balancing" files every month.
Want to bring this to your team?
We work with your real files: the dataset cleaned in the course is the one your area uses every month, and the process stays recorded to repeat itself. Message us on WhatsApp, fill out the contact form or open this site's live chat: we send a proposal with three scope options and a closed price in under 24 business hours, with the DC-3 certificate and Geedle Bot included. The full program is at the data analysis with Excel course and the catalog at training courses for companies.
