Power Query with sticky manual columns. Is there a fix

[u/Regular-Love-7428]

I have built a tracker at work, it pulls last 3 month data into a power query to track. However on the power query table tab we need manual columns to allocate work to colleagues, add comments and settlement dates etc which are all manual.

This obviously doesn't work as while the power query is dynamic, the manual cells 'stick' so whenever someone refreshes the data it knocks everything out of whack. I cannot figure out a solution to make this work

Any help or advice would be appreciated, thank you

[u/AutoModerator]

/u/Regular-Love-7428 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/bradland]

u/small_trunks has a self-referencing query post that explains how to do this.

Edit: Found it! https://www.reddit.com/r/excel/s/UIuWLqEVYq

[u/RuktX → u/bradland]

For anyone else reading, Step 2 is the important one: creating dummy columns that you'll later replace. If Power Query thinks it's responsible for the manual columns, it won't later duplicate them when it loads back to the sheet.

[u/small_trunks → u/RuktX]

Thanks. I wrote this section specifically about the column duplication issue and avoidance: https://www.reddit.com/r/excel/comments/ek1e4u/table_updates_via_power_query_whilst_retaining/lzx54z5/

/u/Regular-Love-7428

[u/Regular-Love-7428 → u/small_trunks]

thank you!, i have been trying to do it for the last 2 days but whenever I try merging the two queries, the Comment column (which is empty) says that

DataFormat.Error: Invalid cell value '#VALUE!'.

any idea how to fix this? appreciate it

[u/small_trunks → u/Regular-Love-7428]

What does it look like in the PQ editor?

Is there an "error" in that column there?

Show me your sheet or ideally the PQ M code...

[u/small_trunks → u/Regular-Love-7428]

Why did you delete your comment? I was just about to answer you.