Cleaning retail data before a Business Central migration

Data migration · 8 min read

Every retail database carries years of quiet damage: the same customer created three times, items nobody has sold in seasons, a unit of measure that means two different things, barcodes that collide. A migration does not fix any of that — it preserves it, in a new system, with new import errors attached. Cleanup before the move is where the damage actually gets repaired. This guide works through the defect classes a retail catalogue and customer file really contain, the documented Business Central and LS Central constraint that governs each one, and the decision that matters most on every fix: does it belong in the old system, or only in the import file?

Cleanup is a scope decision first

Cleanup should focus on the data already approved for migration — not on making every record in the legacy system perfect — and it should happen early, not during final cutover. That scoping step comes first:

1. Classify every data set: migrate, consider carefully, archive. Active master data, current balances, and open transactions migrate. Historical transactions and recently inactive records need a deliberate decision. Obsolete and long-inactive records belong in the archive group.
2. Clean only the migrate group to import standard. Records staying behind need a retention decision, not a formatting pass.
3. Document the cleanup rules as they are decided. Major cleanup rules, manual corrections, and approved exceptions should be recorded and approved by the business owners of the data, so that when a test migration produces a different number, the team can tell whether the difference came from an approved correction, a migration rule, or an error.

Duplicate customers, vendors, and contacts

Start by identifying duplicate customers, vendors, items, contacts, and other master records, then determine which record should remain. For customers, vendors, and contacts, Business Central itself documents the endgame: the Merge Duplicate page merges a duplicate record into the record that survives.

Knowing how that merge behaves is what makes the pre-migration decision an informed one:

• A merge cannot be undone. All fields should be reviewed before confirming. For each field, the original value can be kept, or overridden with the value from the duplicate.
• On confirmation, the duplicate record is deleted and all related records are reassigned to the surviving record.
• Conflicts block the merge. A conflict occurs when at least one primary-key field has the same value in both records but the No. field is different. Conflicts are worked on the Merge Duplicate Conflicts page, where View Details shows the fields causing the conflict. Remove Duplicate removes identical field values other than the No. field from the duplicate and inserts them on the current record; Rename Duplicate instead changes the conflicting value on the duplicate so the duplicate record continues to exist after the merge.
• Contacts have a head start: functionality exists to search for duplicate contacts before the Merge Duplicate page is used.

The practical consequence for migration prep: the survivor decision — which customer or vendor number the business keeps — should be made before the import file is built, so the file loads one record per real-world customer or vendor. Merging after import is possible, but every merge is an irreversible manual operation; a file that arrives deduplicated needs none of them.

Duplicate items: the harder problem

Items get no equivalent rescue. The documented merge covers customers, vendors, and contacts — the item catalogue has to arrive clean, because there is no Merge Duplicate page waiting on the other side. Item cleanup is therefore a spreadsheet discipline, not a system function:

1. Sort and group by the identifiers the business actually uses — item number, description, and barcode — and inspect every cluster that looks like the same product created twice.
2. Pick the survivor item number and record the mapping from every retired number to the survivor. That mapping table is a migration artifact: stock, prices, and open orders referencing a retired number have to land on the survivor.
3. Standardise what the survivor carries: item numbers, SKUs, descriptions, and categories are all part of the documented item-data review, along with units of measure and their conversions.
4. Check for orphaned records — transactions or child records tied to items, customers, vendors, or accounts that will not exist in the new system. A retired item number with an open purchase order pointing at it is an orphan in waiting.

What success looks like: one item card per sellable product, a written old-number-to-survivor mapping for every duplicate removed, and no open document referencing a number that will not be imported.

Dead items: migrate, archive, or drop

Dead stock lines are the biggest single source of catalogue bloat, and the decision is not "delete or keep" — it is the same three-way scope split applied line by line:

One caution cuts the other way: an inactive item that still holds stock, or that an open order references, is not dead for migration purposes. Excluding it creates exactly the orphaned-record problem the cleanup is meant to prevent.

Units of measure and barcodes

These two defect classes cause a disproportionate share of retail import failures, because both carry hard constraints in the target system.

Units of measure. In Business Central, multiple units of measure for an item are set up on the Item Units of Measure page, opened from the item card via the Units of Measure action. If the Base Unit of Measure field on the item card is filled in, that unit is already set up. Each additional unit gets a Code, and the Qty. per Unit of Measure field states how many units of the base unit the new unit contains. Cleanup therefore means: one agreed base unit per item, every legacy unit spelling (each, ea, pcs, piece) collapsed to a single code, and every conversion stated as an explicit quantity per unit — a case that is "about twelve" becomes exactly 12, or it does not migrate. Consistency is not cosmetic: the same unit must be used for each dimension across all of an item's units of measure — kilograms or pounds for weight, for example, but consistently.

Barcodes. In LS Central, the Barcode No. must be unique — the same number cannot appear twice in the table. A barcode number also cannot be a number already used as a No. in the Income/Expense Account table. A legacy file where two items share one barcode, or one barcode is listed against two units, will fail or mis-scan; neither outcome is acceptable at a till. Variants raise the stakes: variants manage products with different attributes such as colours and sizes, each variant can carry its own barcodes, and a variant barcode is recorded with its Barcode No., Variant Code, and Unit of Measure. Where variants are used, the system can also create barcodes automatically for each variant — which makes a pre-migration decision necessary: keep the legacy variant barcodes (cleaned, unique, and tied to the right variant code and unit), or regenerate them, but never import a mixture of both schemes. Note also that the Variant Mandatory if Exists setting on the Inventory Setup page, when active, requires a variant to be specified for any item that has variants — so an item migrated with half-cleaned variant data will demand variants at the POS that staff cannot reliably choose between.

Old system or import file: where each fix belongs

Every fix has two possible homes, and choosing wrong costs time in both directions. Fix in the old system and every later extract inherits the correction; fix only in the import file and the correction exists exactly once — which is right precisely when the correction is a translation to Business Central's structure rather than a repair of the underlying data.

Whichever home a fix lands in, document it. Recorded cleanup rules and approved exceptions are what let the team explain a count difference during test migration instead of re-investigating it from zero.

What never to migrate

• Obsolete and long-inactive master records — archive group, by the scope decision, not by accident.
• Placeholder, test, and "do not use" records that were never real customers, vendors, or products.
• Duplicate records beyond the chosen survivor — one record per real-world entity, with the mapping kept.
• Records tied to parents that are not migrating — orphaned child records fail imports and corrupt reporting; fix the relationship or leave both behind together.
• Data with no owner willing to approve it. Cleanup changes need business approval; a data set nobody will sign off on is a data set that is not ready, whatever its format looks like.

The pre-import cleanup pass

1. Scope agreed and written down: migrate / consider / archive per data set.
2. Duplicates identified for customers, vendors, contacts, and items; survivor chosen for each; item mapping table written.
3. Missing required fields completed; inaccurate contact, address, shipping, payment, and tax details corrected at source.
4. Names and formats standardised; codes and classifications normalised to target values in the file.
5. Units collapsed to one base per item with explicit conversions; barcodes verified unique, variant barcodes tied to variant code and unit.
6. Orphan check run: no child record points at a parent that is not migrating.
7. Cleanup log approved by the data owners.

What success looks like: the first test import's error list is short and boring — a handful of fixable records, not a catalogue of surprises — and every count difference against the legacy system can be pointed at a line in the cleanup log.

FAQ

Can duplicates just be merged after migration instead? For customers, vendors, and contacts, the Merge Duplicate page exists — but a merge cannot be undone and each one is manual. For items there is no merge, so item duplicates must be resolved before import regardless.

Is archiving the same as deleting? No. Archiving means the data is excluded from the new system while remaining accessible in the legacy system or an archive if it is ever needed — it is not deletion without review.

When should cleanup happen? Early in the project, scoped to the data that will actually migrate — not as a cutover-weekend scramble. (For the load itself, see data migration for a retail go-live.)