Power Query shows clean data: did it check the whole finance file?

A clean column-quality bar in Power Query can describe only the rows included in its profile. Microsoft documents that profiling uses the first 1,000 rows by default and offers an option to profile the entire dataset. Check that setting before telling the close reviewer that every amount, account code or date passed. A preview is useful during preparation, but its limited population should not silently become the evidence for accepting a complete financial import.

Record the source file, query step and profiling scope you inspected. The same column can look clean before a conversion and contain errors afterward. It can also look clean after a filter has removed the troublesome rows. The reviewer needs to know both what was assessed and what had already been excluded. A screenshot of a green bar without those details cannot explain whether the intended accounting population was actually tested.

Compare the source record count and relevant totals with the query's initial population. Identify any filters applied by the export, connector or query. Check the entity, period, transaction types and currencies. If a source contains 100,000 records and the profile examines the first 1,000, it covers one percent of the rows. That arithmetic does not make the first 1,000 a representative statistical sample; the export may be sorted in a way that concentrates the difficult records elsewhere.

Consider an illustrative export sorted by account. The first block contains routine bank transactions, while later blocks contain foreign-currency adjustments and records from a legacy system. A clean early profile says little about those later formats. Identify meaningful subpopulations and inspect them deliberately. This is particularly useful after a merger, system change or new transaction type, when the data is unlikely to be uniform even though it arrives in one file.

Preserve the untouched export and perform review on a controlled query or copy. Do not delete error rows simply to make a profile pass. Keep a count and amount for exclusions, with a reason and owner. The final population should reconcile to the source through accepted records, approved exclusions and unresolved exceptions. A smaller clean table is not necessarily an improved dataset if the missing rows represent valid transactions.

Pay close attention to type conversion, locale interpretation, replacement values and joins. A field can remain technically valid while its business meaning changes. A customer identifier converted to a number may lose its intended representation. A date parsed under the wrong convention may still be a valid date. Define what the field is supposed to mean before using its technical validity as the acceptance criterion.

Microsoft's authoring guidance separately warns that automatic type inference examines an initial set of rows and can choose an unsuitable type for later values. Review automatically inserted type steps and set the intended types deliberately. Do not assume that expanding the profiling scope also fixes a previous type decision. Profiling and transformation are related parts of the review, but they are not the same operation.

Inspect a few original-to-final examples for each consequential field. Follow a fractional amount, an identifier with leading zeros, a date near the reporting boundary and a permitted missing value where those occur. Compare the actual stored value, not just the formatted display. A currency format can make a transformed number look familiar while hiding that a conversion already changed it. Record the rule and the example together so another reviewer can repeat the check.

A valid text value can still be an account code that does not exist in the approved chart. A valid number can have the wrong sign. A nonempty entity field can name the wrong company. Add checks against the relevant controlled lists and accounting expectations. The finance owner should define those checks, while the query owner demonstrates how they operate. Technical profiling is an aid to those judgments rather than a substitute for them.

For a journal population, compare debit and credit totals using the source's sign convention and check record identifiers for the expected uniqueness. For an invoice population, distinguish header records from lines before counting. Do not infer the correct test from the file extension. Ask what each row represents and which combinations of fields identify a legitimate record. A perfectly clean file can still double amounts if the downstream report joins it at the wrong level.

Reconcile exceptions by consequence. A missing optional memo may need no correction; an unidentified legal entity can prevent safe posting. Define the handling for each condition and retain the affected references. Avoid one global rule that replaces every missing value with zero or unknown. That can conceal a required investigation and make a later report appear more complete than the evidence allows.

After the query refreshes, compare the loaded output with the accepted population. Confirm the row count, relevant totals and exception count at the destination. A clean editor preview does not establish that a worksheet, data model or receiving system loaded every intended row. If the output has a capacity limit, choose an appropriate destination and document it. Keep the final refresh result tied to the source version used for the review.

If full profiling is expensive, plan when it runs and use targeted checks for routine refreshes based on the actual risk. Do not simply relabel a partial profile as full. The reviewer can approve a documented combination of population reconciliation, field checks and exception monitoring when appropriate. Any untested area should remain visible. Efficiency comes from choosing meaningful controls, not from implying that a lightweight preview proves more than it does.

Retain the query version, mapping decisions, reconciliations and unresolved items with the close or import record. Repeat the affected checks after a source format or transformation changes. The completed review should answer three questions: did the intended records arrive, did their values retain the intended meaning, and were the exceptions handled deliberately? A green profile contributes to that answer only when its population and limitations are understood.