Cleaning Up Dynamics 365 Data Quality When You Have No Team and No Budget

Start by scoping ruthlessly: not all bad data matters equally, so identify the Dynamics 365 columns that feed invoicing, compliance and customer contact, and fix only those first. Then run one repeatable loop. Profile those columns with Power Query to find the real error rates. Fix at source with choice columns, formats, required levels and validation so the same errors stop arriving. Remediate the existing backlog in measured batches with before-and-after counts. Repeat for the next group of columns. And be honest about capacity: when the volume, the number of systems writing into the CRM or the cost of a wrong change exceeds what one person can safely do, bring in help for a defined piece.

This is a checklist for the administrator, operations lead or finance-minded manager who has inherited the problem without a data team. It covers the method. For duplicate accounts and contacts specifically, follow our guide to fixing duplicate customer data in Dynamics 365, which covers detection rules, merge and alternate keys in depth.

Why is trying to clean all of the CRM data at once the wrong starting point?

Because a whole-database cleanup has no finish line, and one person working without a budget will run out of time long before the table is clean. Meanwhile new bad records keep arriving through the same forms, imports and integrations that created the old ones, so the error count barely moves and the effort looks wasted.

Most data quality problems that reach management are narrow. An invoice goes to the wrong address, a VAT or registration number is missing, a customer who opted out is contacted, a credit hold is ignored. Each of those traces back to a handful of columns on a handful of tables. Cleaning those columns fixes the visible problem, earns the credibility to continue and gives you a method you can reuse on the next set.

Which data should you clean first when invoicing and compliance are failing?

Build a short list, not a data dictionary. Ask the people who feel the pain which specific errors cost money or create risk, and trace each one to the table and column it comes from.

• Invoicing: legal account name, billing address, billing contact and email, tax or registration number, currency, payment terms, price list and any customer number shared with the finance system.
• Compliance: consent and contact preference columns, lawful basis or opt-out flags, country, data retention dates and any regulated identifiers your sector requires.
• Customer contact: primary contact per account, email and phone format, and whether the contact is still active at that company.
• The keys that join systems: whichever column an integration uses to match a CRM account to a record in another system, because an error there spreads.

For each column, write down the rule that makes a value correct, who owns that rule in the business and what breaks when it is wrong. If a column is not on the list, it waits. If a report is where the problem shows up, our note on reconciling CRM numbers with finance helps separate a data problem from a reporting one, and our post on finding the root cause of CRM billing errors covers the invoicing side.

How do you measure the real error rate with Power Query?

Profile the scoped columns before changing anything, so you have a baseline and the cleanup is measured rather than guessed. Power Query is available in Excel and Power BI Desktop, and both can connect to Dataverse with your own credentials, so nothing needs to be bought or installed on the server.

1. Connect to the Dataverse environment from Excel or Power BI Desktop and load only the tables and columns on your list, plus the record ID, owner, created on and created by columns.
2. In the Power Query editor, switch on column quality, column distribution and column profile, and change profiling from the first 1,000 rows to the entire data set. The default sample hides most of the problem in a large table.
3. For each column, add a step that tests your written rule: blank, wrong format, a value not in the allowed list, a country that does not match the address, a tax number of the wrong length. Keep the result as a true or false column per rule.
4. Count failures per rule, then group them by created by, owner, source and created on date. The grouping usually shows where errors come from: one import, one integration user, one team or one period.
5. Save the query. You will refresh it after every fix and every batch, and the numbers become your progress report.

Treat the output as a count of records that fail a rule, not a percentage you quote to anyone until the rules have been checked with the business owner. A rule that is wrong produces a confident, wrong number.

How do you stop the same errors arriving again at source?

Fix the intake before the backlog, otherwise every cleaned batch is refilled. Each route that writes to the scoped columns needs its own control, and the right control depends on the route.

• Replace free text with structure: a choice column for values from a fixed list, a lookup to a reference table for things like countries or payment terms, and the email, phone and URL formats on text columns.
• Set requirement levels on forms for the columns that must exist at a particular point. Business required is enforced on forms, not on imports or API writes, so it does not protect you from integrations on its own. Our guide to making a column required at a particular stage covers the stage-dependent case and the problem of people typing placeholder values into required fields.
• Use business rules scoped to the table for simple conditions, and check in current Microsoft documentation which business rule actions also run server side. The guide to Dataverse business rules explains where they apply.
• For validation that must hold for every route, including imports and integrations, the reliable options are an alternate key for uniqueness or a plug-in or a synchronous check that rejects the write. That is developer work, and it is usually the first place where outside help pays for itself.
• Ask whoever owns each integration to validate before writing, and to write to a staging status rather than straight into the customer record where the data is untrusted.
• Switch on auditing for the scoped columns if it is not already on, so future changes show who changed what and when. Our guide to Dynamics 365 audit trails covers what auditing records and what it does not.

After each intake change, refresh the profiling query a week or two later and check that new records created since the change pass the rules. If they do not, a route is still unguarded.

How do you clean the existing backlog safely in measured batches?

Work in small batches that you can count before and after, and that you could undo from a saved copy. The method matters more than the tool.

1. Pick one rule and one slice of records, such as active accounts with open orders that fail the billing address rule. Records that carry revenue or regulatory exposure go first.
2. Create a view in Dynamics 365 or a filtered query in Power Query that returns exactly that slice, and record the count.
3. Export the slice, including record IDs and the current values of every column you will change, and keep that file as the rollback copy.
4. Decide the corrected value for each record. Where the right answer is only known to a person, such as an account manager or the finance team, send them the list with a deadline rather than guessing.
5. Apply the change with the least risky tool that fits the volume: editing directly for a handful, bulk edit on selected records in a view for small groups, or export to Excel and re-import for larger groups, which relies on the hidden ID columns in an exported file to update the existing records. Test the import on a copy in a sandbox first if you have one.
6. Refresh the view and the profiling query and record the after count. Any record still failing gets investigated before the next batch, not carried forward silently.
7. Log the batch: date, rule, slice, before count, after count, file names and who approved the corrected values.

Keep a watch on anything downstream while you work. A corrected customer number or address can change what an integration sends to another system, so agree the timing with the owner of that system first. Where automation runs on update, such as flows or plug-ins that fire when an address changes, check what a bulk change will trigger before running it.

When does the volume need more help than one person can give?

Say it plainly when you reach the limit, because a cleanup that stalls halfway leaves the data harder to trust than before. The signals are practical ones:

• The profiling shows failures in the tens of thousands of records per rule, and the corrected values need lookups against external data rather than a person's judgement.
• More than a few integrations or imports write into the scoped columns, and fixing intake means changing code you do not own.
• Validation has to be enforced server side for every route, which needs plug-ins, custom API or alternate keys.
• Corrections change records that other systems depend on, and a wrong batch would reach invoices or regulatory reports before anyone noticed.
• The same records are also full of duplicates, which have to be resolved before other corrections are worth making.
• The data problem is one symptom of a build that is broken in other ways too.

Bringing in help does not have to mean handing over the whole problem. A useful shape is to keep ownership of the rules and the business decisions in-house and buy a defined piece: an independent measurement of the current state, the intake validation, a scripted bulk remediation with a reconciliation report, or the duplicate cleanup. Our Dynamics 365 health check and technical audit includes data quality and hygiene as one of its assessment areas and produces a prioritised plan you can run with your own people or any partner. Where bad data is one part of an implementation that has gone wrong more broadly, start with our guide to Dynamics 365 rescue services instead.

What does a repeatable data quality checklist look like?

Use this as the loop. Run it once per group of columns, then move to the next group.

1. Name the business problem in one sentence, such as invoices going to outdated billing addresses.
2. Trace it to the tables and columns, write the rule for a correct value and name the business owner of that rule.
3. Profile the columns in Power Query against the entire data set and record the baseline failure count per rule.
4. Group failures by source, owner and date to find the routes creating them.
5. Put an intake control on every route: structure, formats, requirement levels, business rules, and server-side validation where imports and integrations bypass the form.
6. Switch on auditing for the scoped columns.
7. Remediate in small batches, highest-risk records first, with a view, a before count, an exported rollback copy, an after count and a batch log.
8. Refresh the profiling query on a schedule, give the failure counts a named owner and review them monthly.
9. Decide honestly whether the next group of columns is within your capacity, and scope outside help for the part that is not.

Solzet carries out this kind of data remediation as part of Dynamics 365 Customer Engagement and Power Platform engagements, remotely from Yerevan, Armenia, with senior consultants and full-stack developers, directly or on a white-label basis for Microsoft partners. If the cleanup is exposing a deeper question about whether Dynamics 365 is the right place for this data at all, our custom CRM development service covers organisations that need full control of the data model without Microsoft licensing.