Removing duplicate invoice IDs may keep the wrong version

Two rows with the same invoice identifier can represent an accidental repeated export, two versions of one invoice, or several legitimate invoice lines. Removing duplicates before establishing the row's meaning can discard necessary information. Identify the dataset's intended level and the report's purpose first. A current-balance report and a change-history report may need different views of the same source records.

Microsoft warns that Power Query does not guarantee which instance survives duplicate removal. Its authoring guidance also explains that an earlier sort is not guaranteed to remain in force through operations such as grouping, merging or removing duplicates. Sorting newest first and then choosing Remove duplicates can therefore be an unreliable way to select a current financial record. Define the selection rule explicitly and have the query owner demonstrate it.

Use the source system's stable identifier together with any necessary account or entity context. An invoice number reused by two companies should not cause one company's record to disappear. Confirm whether the rows are invoice headers, line items or events. If they are lines, include the line identifier and relevant version information. A header-level duplicate rule applied to a line table can reduce a valid multi-line invoice to one surviving line.

Identify the field that proves revision order. A documented version number or source modification timestamp may be suitable; a download time can mean only when a copy was retrieved. Record its precision, time zone and treatment of missing values. Ask the source owner what happens when two changes share the same timestamp. Do not manufacture an ordering from spreadsheet row position or a filename when the source provides a more authoritative sequence.

Clarify what current means for the report. The latest source revision is not always the version approved for a particular historical reporting release. A report as of an earlier cutoff may need the version effective at that time. Preserve the reporting date and selection policy separately from extraction timing. This prevents a routine refresh from rewriting a previously approved result without an explanation of which later changes were included.

For an illustrative invoice, version 3 shows $700 and version 4 shows an approved corrected amount of $650. A current-state view should select version 4 under that defined rule. Keeping both and summing them produces $1,350; keeping the older row produces $700. Both results are wrong for the intended current obligation. The query needs to select the correct version and retain enough history to explain the $50 change.

Now suppose two rows claim version 4 but contain different totals. There is no safe basis to choose whichever row happens to appear first. Route the conflict for investigation with both source copies and their extraction references. Determine whether one export was corrupted, the version field was reused or the records actually belong to different contexts. An explicit exception protects the report from an arbitrary choice disguised as successful deduplication.

Define treatment of canceled, voided and deleted states with the accounting and source-system owners. The latest event may indicate that an invoice should no longer appear as an open balance, but its history can still matter. Do not select the newest nonzero amount merely to avoid an inconvenient status. Keep the current reporting view and the retained history distinguishable so later users do not sum every event as though it were a separate invoice.

Give the implementer the identity fields, eligible states, version rule, cutoff and tie handling. Ask them to show the selected row and rejected alternatives for a small reviewed population. The technical implementation may use ranking, grouping or another supported method; the finance acceptance criterion is that the same inputs and rule produce the same intended records. Do not prescribe a superficial sort step as the whole control.

Test the query with the input rows shuffled. Add an exact repeated row, an older revision arriving late and a conflicting tie. The chosen current record should remain stable where the rule gives a clear answer, and the tie should become a visible exception. Include one multi-line invoice to show that legitimate detail survives. These tests are small but directly address the failure that an attractive preview can conceal.

Check case sensitivity and text normalization only where the identifier contract justifies it. Two source keys that differ by case may be distinct, even if a human regards them as similar. Conversely, the same business key may have inconsistent formatting that needs a documented correction. Preserve the original key and the approved normalized value where used. A blanket uppercase or trim step should not silently redefine which financial records are considered the same.

Report the number of input rows, distinct business records, selected current rows, superseded versions and unresolved conflicts. Keep totals meaningful by record type and state. A total across every historical version is usually not the comparison for a current balance. Compare the selected view with the source's equivalent current report, accounting for the same cutoff and filters. Investigate a difference at the record level rather than adding a balancing adjustment.

Document whether downstream exports contain the current view, the full history or both. Label them clearly and keep stable references between them. A future analyst should not need to infer the row meaning from the filename. If the report is used for collection, payment or a close adjustment, have the responsible reviewer approve the selection policy and any unresolved exceptions before the output drives those actions.

When a wrong version has already been used, identify the affected refreshes and decisions. Rebuild the report under the corrected rule, compare record-level changes and notify the owners of materially affected outputs. Retain the old result as superseded evidence where the record policy requires it. The repair is complete when version selection is reproducible and the business can explain why each surviving row is the intended one.