Cleaning Data with AI Before Analysis, So Your Numbers Hold Up

Common dirty-data problems in Thai datasets, such as mixed Buddhist and Gregorian years, and how to let AI help clean data in a traceable way.

The sales report shows "Bangkok" selling almost half as much as before. The team nearly cuts Bangkok ad spend, until someone notices that half the data was recorded as "กทม." and some as "Bangkok". Sales had not fallen; they were simply split across several names. This is why data cleaning matters as much as analysis, and it is work AI can help with a lot, if used the right way.

What is data cleaning?

Data cleaning means checking and fixing data so it is correct, consistent and ready for analysis: removing duplicate rows, merging values spelled differently, converting data types and handling missing values. Skip this step and even the most beautiful dashboard leads to wrong conclusions.

Common dirty-data problems in Thai organisations

• Buddhist and Gregorian years mixed: some systems record 2567, others 2024, which scrambles the timeline
• Place names in many forms: กทม., กรุงเทพฯ, กรุงเทพมหานคร, Bangkok
• Thai digits and numbers stored as text: ๑๒๓, or amounts with the word "baht" attached, which cannot be summed
• Phone numbers in many formats: with dashes, without dashes, or starting with +66
• Extra spaces: invisible, but they make "Branch A" and "Branch A " count as different values

The mechanism: let AI create rules, not edit data directly

The key principle: do not send a file to AI and accept a "cleaned" file back without knowing what changed. Have AI help create transformation rules you can review and rerun, such as Power Query steps, Excel formulas or SQL. Next month, when new data arrives, you rerun the same rules, and you can trace why each value was changed.

Steps that work

1. Profile: have AI summarise each column: how many distinct values, how many blanks, and which values look odd
2. Mapping table: have AI propose a mapping such as "กทม." to "กรุงเทพมหานคร", then you review and approve
3. Transform: have AI write Power Query steps or formulas that apply the mapping
4. Validate: compare row counts and totals before and after cleaning

Built-in tools that help

Microsoft Support documentation says Excel has a Clean Data feature for Copilot customers on Excel for the web and Windows. It detects inconsistent text, columns mixing Text and Number formats, and extra spaces. The documentation says it works best with tables of up to 100 columns and 50,000 rows, formatted as an Excel table, and initially performs best in English, so check results on Thai data especially carefully.

Example: merging customer lists from 3 branches

A building materials store with 3 branches wants to merge customer lists for a loyalty programme. The team has AI profile the data first and finds phone numbers in 4 formats and sign-up dates mixing Buddhist and Gregorian years. AI then writes Power Query steps to standardise phone numbers and convert years above 2500 to the Gregorian calendar, and uses phone number as the key to find duplicates. The team reviews each group of suggested duplicates before merging.

Clean data you cannot trace back is still not data you can trust.

Watch out for

• AI may "fix" values that are unusual but correct, such as a large order from a corporate customer, which is real data.
• Do not let AI fill missing values by guessing. Mark them clearly as blank or apply an agreed rule.
• Customer data is personal data under PDPA. Use only tools your organisation has approved and mask data before sending it to external AI.
• Always keep the original file. Never overwrite it.

What to do next

1. Pick one dataset your team complains does not add up.
2. Have AI profile it and note the problems found.
3. Build a mapping table and transformation rules.
4. Save the steps in Power Query or a script so they can be rerun.
5. Set rules for data entry at the source, such as dropdowns instead of free text.

Practise with real tools in the courses Power Query basics for data preparation and Copilot in Excel for summarising and analysing data on Plants.work Learn, then continue with Building Dashboards with AI.