Power Query: Preserve Manual Edits

Key insights

• Power Query refresh: The video shows that refreshing a query replaces the worksheet table and wipes out manual edits like comments or added rows.
  That behavior causes loss of human notes unless you change the query design.
• self-referencing table: The recommended fix is a self-referencing table strategy that snapshots the table after manual edits and merges that snapshot back into the refreshed data.
  This keeps manual fields attached to matching records across refreshes.
• Left Outer Join: Implement the merge using a Left Outer Join so all source rows load and matching manual columns come from the snapshot.
  Expanding only the manual columns (e.g., Comments) avoids duplication of source fields.
• manual column: Key steps are simple: create the source query, add and load a manual column initialized to null, create a snapshot query that references the loaded table, then merge the snapshot back into the refreshed source and expand the Comments column.
  The video walks these steps in order for a reliable workflow.
• unique identifier: Use a stable unique key (Order ID, Unit #, etc.) when merging so the system matches new rows to existing manual notes even if row order changes.
  Correct keys prevent misalignment and preserve context for each record.
• no-code solution: This approach uses native Power Query features (Merge, Expand, Left Outer Join) so you avoid VBA and fragile macros.
  It scales to multiple tables and keeps human context without manual re-entry after refreshes.

Overview

The YouTube video from Excel Off The Grid shows a practical way to keep manual edits when refreshing data with Power Query. The presenter frames the problem clearly: a refresh typically overwrites any manual notes or added rows, which breaks workflows that mix automation and human input. As a result, teams often avoid refreshing or they use fragile workarounds such as copying and pasting data back after each refresh. Therefore, the video proposes a structured method to preserve manual changes while allowing fresh transactions to load automatically.

In particular, the creator demonstrates a “self-referencing” or snapshot approach that merges the current workbook state back into the refreshed data. This technique uses a second query that reads the loaded table and then merges its manual fields into the source query using a join. The video includes a sample workbook and step-by-step guidance so users can replicate the steps in their own files. Overall, the piece aims to bring more reliable human annotations into automated Excel workflows.

How the Technique Works

At the core, the method applies basic ETL logic twice: once to pull fresh source data and once to capture the table after manual edits. First, you create the main query that imports transactions from your external source. Then you load that query to an Excel table and add a manual column such as Comments, initialized to null so the column always exists even when empty. After people add notes or new rows, a second query references that table and acts as the workbook’s memory.

Next, the original source query removes the temporary manual-column creation step and instead merges with the snapshot query. During the merge you match on a unique identifier like an order ID or unit number and choose a Left Outer Join so every source row remains while matching manual values attach. Finally, you expand the merged snapshot to pull back only the manual fields, effectively restoring comments and added rows without losing new source records. This pattern keeps the data refreshed while retaining human context on each record.

The video shows the sequence live, so viewers can see how each step affects the table and how Power Query evaluates the merged data. The presenter emphasizes naming conventions such as tblHistoric for the snapshot to avoid confusion when combining queries. By keeping each step simple and visible, the approach reduces the risk of inadvertently deleting manual annotations during a refresh. Consequently, the method becomes accessible to users with intermediate Power Query experience.

Benefits for Reporting and Collaboration

One clear benefit is that teams can maintain audit notes and working comments directly in the same table that receives periodic updates. Instead of relying on external spreadsheets or risky macros, the snapshot merge stays within native Power Query operations, which improves maintainability. As a result, managers and analysts can refresh data frequently without losing context, which helps when tracking exceptions or explaining anomalies during reviews. Moreover, the method scales to multiple tables and more complex merges, making it suitable for larger reporting setups.

Another advantage is that this pattern reduces dependence on VBA or manual copy workflows, which tend to break after file changes or version upgrades. Because the solution uses standard query steps—create, reference, merge, expand—it fits naturally into an ETL-style process and is easier to document. Therefore, organizations can adopt it without adding heavy technical debt or training overhead. This makes it a pragmatic option for teams that want both automation and human input in the same workbook.

Tradeoffs and Practical Challenges

Despite its strengths, the snapshot approach carries tradeoffs worth considering. For instance, the method assumes a stable unique identifier for each record; when IDs change or duplicates appear, merges can misalign comments and produce incorrect attachments. Consequently, teams must enforce data hygiene or add robust key-generation steps to ensure accurate matching. In addition, merging large tables repeatedly can slow refresh performance, so users should weigh the cost of preserving every manual field against workbook speed.

Another challenge comes from concurrent edits: if multiple users update the Excel table simultaneously, the snapshot can capture inconsistent states or create conflicts. Power Query itself does not handle multi-user merges, so the technique works best in a controlled environment or with clear edit procedures. Finally, some users may prefer keeping manual notes in a separate system for compliance or versioning, in which case the tradeoff of convenience versus formal change control should be evaluated.

Implementation Steps and Best Practices

To implement the method, start by creating the source query and loading it as a table with an initialized manual column such as Comments. Then create a second query that references that table; name it clearly, for example tblHistoric, so it serves as the snapshot of manual edits. Return to the source query, remove the temporary column creation, and add a Merge step that uses a reliable unique key and a Left Outer Join to bring manual fields back into the refreshed dataset.

For best results, test the process on a copy of your workbook and validate matching logic against edge cases like new IDs or deleted records. Tune performance by limiting the snapshot to only the columns you need and by keeping refresh intervals sensible for your environment. Finally, document the workflow for colleagues and establish simple editing rules to avoid simultaneous conflicting changes. By following these practices, teams can gain the flexibility of manual annotations without losing the benefits of automated refreshes.

Keywords

power query keep manual changes, preserve manual edits in power query, retain manual changes after refresh power query, power query preserve user edits, maintain manual edits power query, protect manual changes power query, power query make manual changes permanent, combine manual edits with power query