Why use AI for data cleaning
Cleaning is repetitive and detail-heavy, which is exactly where AI helps. It spots patterns across thousands of rows that your eye misses, like three spellings of the same city. I recommend using it to propose fixes, then you approve each rule so nothing changes silently. A wrong automatic edit can be worse than a visible mess because it looks finished.
The goal is analysis-ready data: one row per entity, consistent types, no duplicate keys, and values that mean what the header says. Every chart downstream depends on this being true, so the few minutes you spend checking pay back many times.
Profile the mess before you fix anything
Do not start deleting. First understand what is wrong, because the fix for a missing value differs from the fix for a misformatted date. Profiling turns vague dread into a checklist.
Ask the tool: show distinct values in the region column of the Orders table. You might see North, north, N., and blank. That list tells you exactly what to standardize.
Prompt: what percent of amount is null or zero in Orders. If 8% is missing, you decide a policy, such as excluding those rows, rather than letting the model guess.
Ask: which customer_id values appear more than once. Duplicates here usually mean a join or import bug, not real customers, and they will distort every total.
I recommend saving the profiling output as a short notes file. In my experience a 5-line summary of what is wrong keeps you from fixing one issue and forgetting another when the file is large.
Handle missing values with a policy
Missing data is a decision, not just a fill. The right move depends on the column and the analysis, so tell the model your rule instead of accepting its default.
Prompt: for the Orders table, set missing region to Unknown and keep those rows; exclude rows where amount is null because they cannot be analyzed. The model returns the exact filter and fill code.
Ask the tool to write the cleaned result to a new table orders_clean and leave the original untouched. This will break if you overwrite the source and later need to recheck your steps.
Request: how many rows were dropped or changed. Knowing you removed 320 of 12,000 rows (about 2.7%) is the kind of number a stakeholder will ask for.
Standardize inconsistent text
Text columns are where AI earns its keep, because the variants are endless and the rules are fiddly. You still confirm the mapping so a real value is not collapsed into the wrong one.
Prompt: map region values like N., No., North, north all to North in the Orders table. Review the mapping dict before applying it so a distinct region is not swallowed.
Ask for a step that strips leading and trailing spaces and applies consistent casing to the product_name column in Products. Hidden spaces are a classic cause of false duplicates.
Request: flag any amount outside the range 0 to 100000 in Orders. A value like $9,999,999 usually means a units or sign error that should be reviewed, not auto-fixed.
Deduplicate rows safely
Duplicates inflate totals and bias any average. The trick is defining what a duplicate means, because two rows can look alike yet be real separate events.
Prompt: remove exact duplicate rows in Orders where order_id is identical. Using order_id as the key prevents dropping two legitimate orders placed by the same customer.
Ask the model to show the duplicated rows side by side first. This will break if you delete on the wrong column and erase 15% of real records thinking they were copies.
Confirm the row count dropped only by the expected amount. If Orders went from 12,000 to 9,800, you removed 2,200 duplicates, a number worth noting in your cleaning log.
Parse dates and numbers correctly
Wrong types are silent killers. A date stored as text will not sort chronologically, and a price with a dollar sign will not sum. AI can write the parse, but you verify the result.
Prompt: convert order_date from text to a date, handling 2024-01-03 and 03/01/2024. Check that January 3 lands in January, not March, after the parse.
Ask: remove $ and commas from the price column in Products and convert to a number. A value like $1,299 must become 1299.0 so it sums correctly.
Print the minimum and maximum date and the min and max amount. An order dated 1970 or an amount of -100% usually signals a parse failure you should fix before analysis.
Validate the clean dataset
Clean is a claim you prove, not a feeling. A short validation routine catches the mistakes that would otherwise surface in a meeting.
Ask the tool to profile orders_clean again. The missing-value percent should now be 0 where you filled, and distinct regions should match your map with no stray spellings.
Sum amount in the cleaned table and compare to a manual sum of the same rows in a spreadsheet. For a $250,000 monthly total, a $200 gap means a row was dropped or altered you did not intend.
Write down each rule you applied and the rows affected. This turns a one-off cleanup into a repeatable script you can run next month on fresh data.
I recommend keeping the original file read-only and naming the output orders_clean_2024. In my experience a dated, clearly named copy saves you when someone asks what changed three weeks later.
Worked example: cleaning a product export
Suppose the Products table has product_name with trailing spaces, a price column with dollar signs, and 3% duplicate rows. Here is how the assistant might draft the fixes that you should review before running.
Prompt: from Products with product_name (str) and price (str like $1,299), trim spaces, strip $ and commas, convert price to float, and drop exact duplicate rows. Return the code with a comment each line.
Pick one row where price was $1,299. After cleaning it must read 1299.0. If it reads 1.299 or 12990, the comma handling is wrong and you must fix it before scaling.
If Products had 5,000 rows and 150 were duplicates, the clean table should have 4,850. A different number means the duplicate key was not what you thought.
Prompt templates you can reuse
Cleaning recurs every month, so save these. Each one names the table and columns so the model works on your real data, not a guess.
Prompt: profile the table Orders, list distinct values and missing percent for region and amount, and show duplicate customer_id counts. Use this first on every new file.
Prompt: map these region variants to canonical names, trim spaces, and title-case the column. Paste your variant list so the model does not invent mappings.
Prompt: after cleaning, re-profile and report rows changed, missing percent, and any value outside expected ranges. This becomes your acceptance check.
Common mistakes that ruin clean data
A common mistake is accepting an automatic fill of missing amounts with the column average. This will break if those rows were refunds or test orders, because you invent revenue that never happened.
This will break if you dedupe on customer_id instead of order_id. You collapse every repeat customer into one row and cut your order count by 30% or more with no error.
I recommend stating the dedupe key in words before running the code. In my experience 9 out of 10 bad cleanups I have reviewed came from a wrong or missing key definition, not from the tool itself.
Where AI cleaning fits your stack
AI drafts the rules; your existing tools run them. In Excel, our excel-power-query guide shows a click-based path, and remove-duplicates-in-excel covers the manual version. In SQL, data-cleaning-with-sql applies the same logic server-side. For code, python-regex-data-cleaning handles tricky text patterns the model can describe for you.
When a value needs a pattern fix, ask the model for the regular expression and a sample match. You paste it into your tool and test on a few rows before applying to all.
Decide drop versus fill yourself and tell the model. The assistant should execute your rule, not invent one, because only you know the business meaning of a blank cell.
Next steps checklist
You now have a repeatable cleanup: profile, define rules, apply on a copy, validate, document. Save your three templates and run them on the next messy file you receive. The aim is a dataset you trust, not just one that looks tidy.
Store the profile, standardize, and validate prompts with your real table and column names. Reuse them on the next export without rewriting.
Take a messy file you own, run the profile, apply one rule at a time, and reconcile one total by hand. That single pass builds the habit faster than any extra reading.
