Every company I've talked to underestimates their data problems. Here's what we discovered:

• 40% of our customer records had outdated contact information
• Over 300 duplicate items lurked in our inventory, with inconsistent naming
• 12% of GL transactions had been miscoded over the years
• Nearly 200 vendors appeared multiple times with slight variations in name

None of this was obvious in our day-to-day operations because we'd built workarounds and tribal knowledge to compensate. Longtime employees "just knew" which codes to use and which to avoid.

Business Central couldn't rely on those mental shortcuts. It needed clean, consistent data to function properly.

The Real Costs of Skipping Data Cleanup

The price of cutting corners on data preparation goes beyond just technical headaches:

Financial Impact

• Extended consulting hours: Our implementation partner had to spend weeks helping fix data issues that should have been addressed upfront
• Internal labor cost: Three staff members spent 80% of their time for two months fixing data problems
• Delayed go-live: We missed our target launch date by a quarter, delaying efficiency gains

Operational Disruption

• Failed test migrations: We had to restart the migration process multiple times
• Training confusion: Users couldn't properly train because test data didn't make sense
• Post-launch firefighting: Issues that slipped through created daily emergencies for weeks

Reputation Damage

• Loss of confidence: Executive team questioned the entire project's viability
• Implementation partner frustration: Strained relationship with our consultants
• User resistance: Staff became skeptical of the new system before even using it

A Practical Approach to Data Cleanup That Actually Works

After learning these lessons the hard way, here's the approach I wish we'd taken from the start:

1. Assess Your Data Reality (2-3 Weeks)

Don't guess—know exactly what you're dealing with:

• Run collision reports to find duplicate records
• Validate key fields (email formats, phone numbers, addresses)
• Check referential integrity between related records
• Analyze transaction patterns to identify miscoded entries

What worked for us: We created a data quality scorecard for each major data area (customers, vendors, inventory, GL), giving us a clear starting point.

2. Make Strategic Decisions About Historical Data (1 Week)

Not all data deserves to migrate:

• Closed transactions over 7 years old: Archive separately
• Inactive customers with no purchases in 3+ years: Archive or flag as inactive
• Obsolete inventory items: Mark as discontinued, don't migrate dead stock
• Vendors you no longer use: Archive rather than migrate

What worked for us: We created a simple decision matrix with stakeholders to agree on what would move and what wouldn't. Getting this buy-in early prevented arguments later.

3. Fix the Biggest Problems First (4-8 Weeks)

Focus your cleanup efforts where they'll have the most impact:

Customer Data Cleanup

• Standardize company names (decide on "Inc." vs. "Incorporated" and stick with it)
• Update or remove outdated contact information
• Merge duplicate records (carefully preserving transaction history)
• Validate tax settings and payment terms

What worked for us: We assigned each major customer to a salesperson to verify and update contact information—distributing the workload and improving data quality.

Vendor Data Cleanup

• Create consistent naming conventions
• Verify tax identification numbers and payment details
• Consolidate duplicate vendors
• Review and standardize payment terms

What worked for us: We discovered we had the same vendor set up 4 different ways, paying different prices to each. Fixing this alone saved us thousands.

Item Data Cleanup

• Standardize naming conventions and descriptions
• Verify units of measure are consistent
• Update costing methods and GL postings
• Delete or archive obsolete items

What worked for us: Creating a standard item naming convention and updating all descriptions accordingly made our search functions actually useful for the first time in years.

General Ledger Cleanup

• Reconcile subledgers to general ledger
• Correct miscoded transactions for current fiscal year
• Review and standardize allocation methods
• Clean up dimension values

What worked for us: We focused on fixing current year transactions, which gave us a clean starting point without having to correct years of history.

4. Create Data Governance Rules (1-2 Weeks)

Prevent bad data from coming back:

• Develop data entry standards for new records
• Create validation rules where possible
• Assign data ownership to specific roles
• Establish regular data audits

What worked for us: We created simple one-page guides for creating customers, vendors, and items that everyone now follows.

5. Validate Before Migration (2-3 Weeks)

Verify that your cleanup efforts worked:

• Run trial migrations to test data integrity
• Validate key reports in the new system
• Check high-value customer and vendor accounts manually
• Verify beginning balances match

What worked for us: We created a validation checklist with 50+ specific checks to perform after each test migration.

Tools That Actually Helped Us

These practical tools made our data cleanup manageable:

For Basic Cleanup

• Excel with Power Query: Surprisingly powerful for finding duplicates and standardizing formats
• SQL Server Data Tools: For more complex data transformation if you have the skills
• Business Central Configuration Packages: Helped us organize and validate data before import

For Ongoing Data Governance

• Data quality dashboard: We built a simple Power BI report showing data quality metrics
• Weekly exception reports: Automated checks that flag new data issues
• Field validation rules: Built into Business Central to prevent bad data entry

The Unexpected Benefits of Proper Data Cleanup

Though painful, our data cleanup journey delivered surprising benefits:

• We discovered we were charging inconsistent prices to the same customers through different channels
• Several "lost" inventory items worth $18,000 resurfaced during physical count reconciliation
• Vendor consolidation gave us better negotiating power and volume discounts
• More accurate forecasting with clean historical data improved our purchasing decisions

Recommended Data Cleanup Timeline

Based on our experience, here's a realistic timeline for a midsize company:

Common Pitfalls to Avoid

These mistakes cost us dearly:

1. Assuming Your Data Is "Good Enough"

Reality check: If you've been on your current system for more than 3 years, you have data problems you don't know about.

2. Underestimating the Time Required

Reality check: Data cleanup typically takes 30-40% of your total implementation time. Budget accordingly.

3. Trying to Clean Everything

Reality check: Focus on the critical data elements first. Some historical data may not be worth the effort to clean.

4. Failing to Involve Department Heads

Reality check: Accounting alone can't validate all data. Sales needs to verify customers, purchasing needs to check vendors, etc.

5. Not Testing Thoroughly After Cleanup

Reality check: Validate your data extensively after cleanup. One missed issue can cascade into hundreds of problems.

Final Thoughts: What I'd Do Differently Next Time

If I could go back and restart our Business Central migration, I would:

1. Start data cleanup 3 months earlier than the implementation
2. Budget 20% more for internal resources dedicated to data preparation
3. Create clearer data standards before beginning cleanup
4. Run a "mock migration" early to identify problems sooner
5. Involve end users in data validation to distribute the workload

Data cleanup isn't the exciting part of an ERP implementation—but it's the foundation everything else rests on. Invest the time upfront, and you'll avoid the painful lessons we learned through trial and error.

The good news? Once we fixed our data issues, Business Central delivered on its promises. We now have visibility and efficiency we couldn't have imagined with our old systems—but we could have gotten there much sooner with proper data preparation.