Clean up your data before migration

Bad master data comes along if you do not clean it up first. Duplicates, customers with no activity and items nobody has sold in ten years become noise in the new system and use up your data capacity. This article shows how to clean up before a migration, what you can leave alone and how to document the decisions so that the auditor and users can follow them. It suits both small and larger projects.

Why clean-up comes first

After go-live it is more expensive to fix. Every faulty customer produces wrong invoices, and every old item turns up in searches. Cleaning up before the migration is the cheapest quality assurance you can do.

Set a deadline for the clean-up, and let a subject-matter person own each type of master data. The accountant owns customers and vendors, and the warehouse owns items.

Duplicates

Find customers and vendors with the same company registration number, the same address or almost the same name. Sort in Excel by registration number and by name without the company form. Decide which record survives and what happens to the open entries on the other.

Business Central also has a function for merging duplicate customers and vendors. Check on Microsoft Learn whether and how it can be used in your version, and do it in a test environment first. Keep the list of merged records, so that nobody has to guess why a customer disappeared.

Blocked customers, old items and reconciliation

Customers that are blocked in the old system should not simply be transferred without a decision. For each one, decide whether the block still applies, and either carry the block over or do not move the customer.

A simple rule could be that customers with no sales and no open entries in the last three years are not moved. The rule is an example. Set the cut-off yourself based on your needs and your local accounting rules.

Items with zero stock, no movement for a long time and no open orders are candidates for being retired. Check bills of materials and substitute items before you remove anything.

Also check units of measure, item categories, posting groups and weight. Missing groups stop posting the first time the item is used.

Keep a list of what has been removed and why. Reconcile counts and balances before and after the clean-up, so that you can explain every difference.

Number of active customers, vendors and items before and after.
Customer and vendor balances at the cut-off date.
Inventory value at the cut-off date.
List of merged and removed records.

Vendors and Excel as a tool

Review vendors with particular care, because wrong bank details can lead to misdirected payments. Check IBAN, SWIFT, payment terms and currency, and ask the vendor for confirmation if anything is unclear.

Remove vendors with no movements for a long time, but keep those where you need to be able to document old purchases.

Export master data to Excel and use filters, sorting and duplicate highlighting. A pivot table quickly shows how many customers share the same postal code or the same contact person. Keep the original export unchanged and work in a copy.

Let colleagues review the list of records proposed for removal. It takes half an hour, and it often catches customers who are active after all.

Who does what

Spread the work so that the clean-up does not end up with one person. Let the subject-matter people decide, and let a project manager follow progress on a simple list with status per type of master data.

Put the clean-up early in the plan. It depends on people's time, not on the system, and it cannot be rushed in the last week.

How to move on

Addverk is a new Business Central partner. We can help extract, analyse and clean up data before a migration. See our implementation service and the prices page.