A Power Query row index is not a permanent transaction identifier

A Power Query index can give every row a convenient number, but that number describes a position in the table at the step where it is assigned. It does not automatically become the source transaction's permanent identity. This matters when staff use an index to attach review notes, approval decisions or later accounting updates across refreshes.

Microsoft describes the index command as adding explicit position values, with a configurable starting value and increment. Its examples use the index to support transformations. The business must decide whether a positional reference is sufficient for its own use. A column called ID does not acquire stable identity merely because the numbers are unique within one output.

Trace a questionable note back to the record on which the reviewer originally acted. Preserve the previous output and the refreshed version. If the note now appears beside another invoice, establish whether the attachment was based on row position, a regenerated index or a genuine source key. Each arrangement requires a different repair.

Suppose a fictional output lists invoices A, B and C with an index starting at one. A reviewer records approved beside index 2, meaning invoice B in that version. A later source includes invoice AA between A and B in the chosen order. When the index is regenerated, A is 1, AA is 2, B is 3 and C is 4.

Joining the old approval to the new index 2 would attach it to AA. The original reviewer never examined that invoice. The failure does not require duplicate index values or a broken formula; each output can contain a perfectly unique sequence. The problem is using a temporary position as a relationship across two different populations.

Deletion and reordering create related risks. Removing an earlier row can shift later positions, while a changed sort can reorder existing records. Identify the exact transformations before the index step. A deterministic sort may make one unchanged input reproducible, but it does not prevent new records from moving existing positions in the next refresh.

Prefer the source system's stable record identifier where available and appropriate. Preserve the source account or entity context needed to distinguish it. An invoice number shown to a customer may repeat in another company or within another supplier's sequence. Confirm the intended uniqueness rather than assuming the most readable field is the safest key.

Define the record grain. A single invoice may contain several lines, allocations or payments. If reviewers annotate a line, an invoice-level key is incomplete. Use the source's line identity or an approved combination that identifies the actual object under review. Document how corrections, replacement documents and deleted records are represented.

If the source provides no suitable stable identifier, ask the system owner to design and persist an appropriate mapping. A locally generated key can be useful when its assignment is retained and governed. Recreating a sequential key independently on every refresh does not provide that persistence. Do not imply that concatenating a few convenient fields solves every collision or change problem.

Store a review decision with the source key and enough context to establish what was reviewed. Include the relevant record version or material values when the process requires it. An approval for an invoice at $1,000 should not silently authorize the same invoice after a correction to $1,600 merely because its stable key remains unchanged.

Keep the review table separate from regenerated display positions. The output can still show an index for navigation within a frozen report, but the persistent association should use the approved business key. Test that a note remains with the same invoice when another invoice is inserted before it. Also test a changed or removed source record and the resulting exception behavior.

Avoid treating unmatched review notes as disposable clutter. They may refer to records excluded by a new filter, deleted upstream or outside the current reporting period. Preserve their identity and investigate the reason. A refresh should not erase the evidence of work already performed simply because that record no longer appears in the current view.

Use retained earlier outputs to reconstruct which record each positional note originally described. Match the old position to its old source key, then map that key to the current record where appropriate. Preserve the method and identify cases where the earlier output is unavailable. Do not transfer a note based only on the current row occupying the same number.

Have the review owner resolve ambiguous associations. Where the note's intended record cannot be established, mark the decision as unresolved and repeat the necessary review. It is better to acknowledge missing evidence than to manufacture an approval by choosing the nearest amount or date. Keep the uncertainty visible to anyone relying on the final status.

Check any actions already taken from the misattached decisions. A note used only for an analyst's reminder has different consequences from a status used to release an import or payment. Follow the company's correction process for those downstream actions and preserve both the original association and the supported repair.

An index remains useful for ordering, grouping transformations or referring to a row in a specific frozen extract. Label that scope clearly. A request to inspect row 27 of a named version can be precise, while a standing instruction to update transaction 27 across all future refreshes may be meaningless without a stable source relationship.

Record the key definition, expected uniqueness and handling of changed records in the query handoff. Use a small example containing an insertion, deletion and correction to demonstrate the chosen behavior. The finished process should preserve the link between a human decision and the business record reviewed, even when the surrounding table grows or changes order.