I've found a way to approach this. See my logic (not code) below:
TableA = An extract of the Excel table containing the blank column in which you've put your notes or whatever (we'll refer to it as "Notes").
TableB = SharePoint or SQL database dataset that you're using to update TableA
TableC = The normal query in PowerQuery that typically refreshes your Excel table containing your Notes column. (You'll understand this by the end).
1. In PowerQuery, pull TableA (as a connection only - no output to an Excel worksheet).
- Keep the primary-key column(s) associated with the Notes column and also keep the actual Notes column. If you don't have a primary key, you'll have to manually create one in PowerQuery by adding a new column containing concatenated field values that won't change when TableB refreshes and overwrites TableA in Excel.
- Example: transaction ID or Date, Email Address, and Issue might combine to create a unique identifier.
2. Pull TableB in PowerQuery.
- Add a column onto TableB containing the same Primary Key that you made from TableA (if needed).
3. Join/merge TableA onto TableB using the primary key column you have or that you created in step 1.
- This is what becomes TableC.
- Move columns to where you need them on the final output table.
- Remove any columns associated with any custom Primary Key columns you created in the process.
4. Use TableC to overwrite the original Excel table.
It might be a bit of a pain. But, it's necessary if you want to keep your values manually filled into a blank column you created when the query refreshes.
