How to clean messy business data before building a report

Do not connect every system at once. Pick the source that matters most for the first question, such as sales, invoices, or stock. Get one source right before adding the next.

A narrow, well-understood dataset is more useful than a broad, unreliable one. It also makes it easier to spot problems early.

Write down what each important field means. A date can be an invoice date, a payment date, or a delivery date. They are not interchangeable. A customer can be a billing entity, a location, or a contact.

Clear definitions prevent most reporting arguments. They also make the report easier to maintain when someone new takes over.

Duplicates usually appear when data is exported more than once or when several systems hold the same customer or order. Use a stable key and check the row count before and after cleaning.

Keep an exceptions list rather than deleting uncertain records silently. Someone may need to review them.

Dates, currencies, product codes, and category names should use a consistent format. Mixed formats cause broken joins and misleading totals.

A simple mapping table is often enough. Document any manual mappings so they can be reused.

Before building the report, reconcile the cleaned data with a trusted source: the bank, the ledger, or the source system. If the totals do not match, stop and fix the data.

Do not hide the difference in a chart or an “other” category. A reconciliation difference is a finding, not a design choice.

Write a short note explaining where the data comes from, how it is cleaned, and who owns it. Include the refresh frequency and any known limitations.

The next person should be able to run the process without guessing. That documentation is part of the data product.