An AI assistant can draft Power Query steps quickly, but fluent M code is not evidence that the resulting figures are correct. This guide combines Microsoft's documentation on merges, privacy and query performance with the UK Government Data Quality Framework, published on 3 December 2020. The numeric examples are fictional exercises, not claims about real customer files or field-tested results.
Define the business question and acceptance rule
Before asking an AI assistant for M code, identify the authoritative table and the decision the output must support. In a fictional exercise, an orders file has one thousand lines and a price table maps product codes to prices. The goal is to calculate each order value without changing the number of valid orders. This is more testable than a vague request to clean the data.
Keep a read-only copy of the input and record its extraction date, time zone, row count and control totals. Write explicit acceptance rules: no valid order disappears, a join cannot duplicate an order value, and unmatched product codes remain visible. A reviewer needs to understand the reason for a failed check, not merely see a green success indicator.
Establish a measurable baseline
Count rows and columns, missing mandatory fields, distinct business keys and totals for quantity and value before changing any data. These controls reveal problems such as an accidental text conversion, silent filtering or a many-to-many join. Store the baseline in a separate audit query so that validation does not alter the production output.
Compare source measures with transformed measures and investigate every unexplained movement. If the starting total is 250,000 currency units and the result is 251,200, the difference of 1,200 requires an explanation even when the table looks polished. A higher total is not evidence of better data; it might be a duplicated reference match.
Test joins before expanding columns
Microsoft's Power Query documentation explains that merges depend on matching data types, key order and the chosen join kind. For an orders-to-prices lookup, check that product codes have compatible types and that each product and effective date has one authoritative price. Otherwise expanding the merged column may multiply transactions and inflate revenue.
Use a left outer join when all orders must be retained, and a left anti join to list orders with no reference match. An inner join can silently discard exceptions, so do not select it just because the resulting table is smaller. Report matched and unmatched counts, and do not replace missing prices with zero unless an approved business rule requires it.
Check types, nulls and regional date formats
An assistant may interpret 03/04/2026 as March fourth or April third depending on regional settings. Specify the locale and the intended date pattern before conversion, then test known records. Check decimal separators, negative values and identifiers with leading zeros as well. A syntactically valid transformation can still destroy the meaning of a business field.
Distinguish null, which may mean unknown, from zero, which usually denotes a measured absence. Compare null rates in critical fields before and after transformation. A sudden drop can signal fabricated default values rather than improved completeness. Ask the assistant to propose explicit, testable rules, and have the data owner approve them.
Review generated M code line by line
Ask the assistant to explain each transformation step, its assumptions and the columns it expects. Inspect the Advanced Editor before execution and reject unexpected web calls or external connections. Compare the code against the real schema: a function can run without an error while joining the wrong keys or filtering a valid category.
Create a small adversarial fixture with a duplicate key, invalid date, null value, negative price and unmatched order. Calculate the expected output manually, run the query and compare every case. Save the before-and-after M script and a short change log naming the reviewer. Reproducible tests are stronger evidence than a persuasive AI explanation.
Protect confidential data and source boundaries
Microsoft documents privacy levels in Power Query because combining private and public sources can create unintended data flows. Classify payroll, customer and contractual information under your organization's rules, and do not disable privacy safeguards merely to make a refresh faster. Inspect each connection, credential scope and destination before approving the workflow.
For cloud AI assistance, share synthetic rows or a non-sensitive schema unless your organization has authorized real-data processing under suitable controls. Replacing names alone may not eliminate re-identification risk. Document who can access the files and how long they remain stored. Data protection is part of trustworthy analysis, not an optional final polish.
Retest refresh behavior and performance
A query may work on a hundred-row sample yet fail when next month's file changes column names or includes unexpected values. Refresh against at least two representative extracts, including a deliberately difficult one. Record row counts, totals, errors, refresh duration and the timestamp of the last successful run, so stale results are not presented as current.
Microsoft explains that query folding can delegate supported transformations to the source, but behavior varies by connector and step. Measure actual refresh performance instead of promising a universal speedup from rearranging steps. If folding breaks, credentials expire or the source schema changes, make the failure visible and prevent an unverified dataset from silently replacing the approved output.
Sign off with an auditable acceptance report
The UK Government Data Quality Framework distinguishes accuracy, completeness, uniqueness and consistency in relation to a dataset's intended use. Turn these dimensions into an acceptance sheet listing rows, duplicates, missing fields, control-total differences and unresolved exceptions. No single percentage score can replace the process owner's judgment about whether a dataset is fit for purpose.
Attach the source date, M-code version, reviewer, test results and known limitations to the final deliverable. A useful conclusion might be: acceptable for internal trend analysis, but not for invoicing until five products without valid prices are resolved. Such a bounded decision is more credible than claiming that AI has repaired the workbook completely.
Official resources
- Microsoft Learn — Merge queries overview, join kinds and key matching (checked 29 October 2026) ↗
- Microsoft Learn — Privacy levels and preventing accidental data transfer (checked 29 October 2026) ↗
- Microsoft Learn — Query folding performance examples (checked 29 October 2026) ↗
- UK Government Data Quality Framework — published 3 December 2020 (checked 29 October 2026) ↗
No online income or trading system guarantees profits. Results depend on skills, costs and circumstances.
Disclaimer ↗