Data cleansing in Business Central for customers, vendors and items

The problems Business Central data cleansing solves

The same customer sits in the system three times under slightly different names. Vendors have no payment terms. Items lack a posting group, a unit of measure or a category. Addresses have the state typed four ways. Records for businesses that closed long ago still show up in every lookup.

Each of these has a practical cost. Duplicates split a customer's balance and sales history. A missing posting group stops a document from posting. Inconsistent addresses break tax calculation and shipping integrations. Dead records slow people down and invite mistakes. Most users have learned to live with these, which is why they stay.

Profiling data before Business Central data cleansing

We begin by counting. For each main table, we report how many records exist and how many have had no activity for a long period. We also report which required fields are empty and which values look like variations of one another. The result is a short findings document, not a vague impression.

That document lets you set priorities. Not every flaw is worth fixing. You can leave an old vendor with no open entries behind in a migration, or block it in a live system. So effort goes to records that are active and to fields that drive posting, tax, pricing and planning.

Tools for Business Central data cleansing

Several standard features do the heavy lifting. Customer, vendor and contact cards have a Merge With action. It combines two records and moves their entries to the one you keep. Contacts have a duplicate search based on search strings you can set. The Blocked field retires a record without deleting its history.

For bulk corrections, Edit in Excel lets a user change many rows on a list and publish them back with validation. Configuration packages export a table, accept corrected values and apply them with error reporting. For every bulk change, we first use a sandbox copy. Only then do we touch production.

Business Central data cleansing ahead of a migration

A migration is the easiest moment to clean, because every record has to pass through a template anyway. We extract from the old system and apply agreed rules in a staging workbook or database. Then we return exception lists to the people who know the answers: sales for customers, purchasing for vendors and operations for items.

We record each decision as a rule, for instance which of two duplicate accounts survives. That way, the same logic can run again for each trial load and for the final cutover. Cleaning by hand in the old system once, and then extracting again later, tends to lose the work.

Keeping Business Central data cleansing results in place

Data gets dirty again unless the entry points change. Customer, vendor and item templates fill in default posting groups and terms when someone creates a record. Mandatory fields and approval workflows for new records catch omissions. Fewer people with permission to create master data also means fewer variations.

We also leave behind a small set of saved list views that act as an early warning. They show items without a category, customers without a tax area and vendors with no payment method. A named owner who looks at them on a regular schedule is worth more than another large cleanup later.

Business Central data cleansing questions

Can duplicate customers be merged after transactions have been posted?

Yes. The Merge With action moves ledger entries, documents and related records from the duplicate to the record you keep. Then it removes the duplicate. We test each merge pattern in a sandbox first, because a merge does not reverse with a click. So you see the result on a copy before anything changes in live data.

Should we delete old records or block them?

Usually block. Business Central stops you from deleting records that have entries in open fiscal periods, and deleting removes context from history. Blocking hides the record from new transactions and keeps the past intact. You can also reopen a blocked record if you need it again. That makes blocking the safer habit.

Who should decide which record is correct?

The department that uses the data. We find and present the conflicts and apply the decisions. But only your sales, purchasing and operations people know which address, terms or description is right. We never guess on their behalf. That keeps Business Central data cleansing honest, and it keeps the final data in the hands of the people who rely on it.

Do you clean transaction data as well as master data?

We do not edit posted entries, because they form the audit trail. Instead, you correct errors there with reversing or adjusting entries. Cleansing concentrates on master records and setup, which is where most quality problems start. Fixing the source also prevents new errors, so the same mistakes do not return next month.

Can cleansing be done without a migration project?

Yes. Many clients ask for it in a live system, often before a reporting project or an integration that will expose the flaws. The method is the same. We simply take extra care, because users are working in the data while we clean it. We also pick quiet moments for each bulk change.

Related data services

Cleansing is one part of looking after data. The pages below cover moving data, governing it over the long run, and the guide to the configuration packages we use for bulk corrections. Read them in any order. After that, we can scope your Business Central data cleansing, starting with a profile of the records you rely on most. For the product detail, see Business Central documentation on Microsoft Learn.