Add manual information into a query | Power Query | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Get the example file ★

★ About this video ★
There are many scenarios where raw data itself isn't enough to complete a task efficiently. For example, we may need to add manual information, such as categorization, commentary, or reference numbers.

In this video, we look at this process and understand how to add manual information into a query.

0:00 Introduction
0:36 Example and process
1:31 Step 1: Get data & unique reference
4:43 Step 2: Add manual information
5:24 Step 3: Merge manual info with the original query
7:26 Step 4: Refreshing the data and testing
8:56 Important information
9:27 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[vickydicky]

But I always get column commentary 2 whenever i refresh the data ?/ Please help...i deleted the commentary 2 column but again i refresh another column it creates

[djl]

FINALLY! Somebody has a solution to this! I've been thinking about this for YEARS!

[teresa]

I've tried this three times, and when I go to delete the manual entry, it comes back when I refresh. I can't identify the step I missed?

[GrainneDuggan_Excel]

Thanks for the video Mark. The need to add comments which are not lost on query refresh is a problem that crops up often. So glad you found such a simple/straight forward way to handle this.

[chriswall]

"Witchcraft! Burn the witch!"🤣

Presumably if the source files go resorted or removed from Files folder then this would break the unique reference?

[GeertDelmulle]

I’ve been using this technique since 2019 and can attest: this manual data finds itself in a bit of a closed loop, so it can easily be lost.
It takes extra steps to get it back in again. Backups are your friends.

[sirsath]

It didn’t work out When I remove 2 files from the folder and refreshed it

[aaronren]

DIDNT WORK, i followed your steps but after loading new data, my comments still gone to other rows....

[sirrobt]

Dude i've been looking for this for a while. I've learned most of this by deduction, but always had to control the imput order of files so i wouldnt lose my manual input order. Self referencing logic didnt make much sense to me but i got the jist of how to use it at least. I was wandering: is it possible to add some of the manual inputs automatically through a partial match with a dictionary table?

[micahmungal]

If i delete something from the source, it deletes the other commentary.

[babisflou]

So the commentary 2 column appears only on the first refresh and not on every refresh?
If this get uploaded on sharepoint, wouldn't it return errors about access of the current excel file to its self (the table we are refreshing)?

[christoffvanrensburg]

Brilliant! Thank you. Only issue I'm seeing is the second Comment column is coming back after I refresh the data. I deleted the Comment2 column after loading the query, but then it just reappears again. Any ideas would be great.

[Idolon]

Sir, I thank you very much for this. I am reasonably fluent in PQ, but the possibility to add a 'comment' or a 'ToDo / manage later' bothered me for some time. I played along with your video with some IP-scan data (pretty unique in my home-network) but I found out that the procedure only works if the data is from an external source (e.g. csv) and not part of the same workbook, be it a range or an Excel Table.
Could you please comment on if I'm correct on this? 🇳🇱

[Rotceh]

This is in one word: “GREAT”. Thanks for sharing.

[francescamontecucco]

Thank you so much!! That is great, it really helps me finally ti maintain several columns of comments. Great!

[MgjlGye]

Hi! When i add a column to table (step 2) it doesnt read as part of the table, what should i do?

[txreal]

When I "Create Connection only" on "Manual Info Query", I did not get "Commentary2" in the original Query Table.
Am I missing something? Thanks

[zaheer]

This is brilliant. I don't have immediate use for this but it's a great weapon to have in my arsenal!

[jgiordimaina]

Thank you for the great easy-to-follow video. It worked great! The problem I am having is that when I sort the column or the list of rows changes because the data in the source (in my case Jira) changes, than the comments are no longer linked to the unique ID. Any tips you can provide to solve this problem?

[RasmusVinther-wg]

From my lack of more than beginner query understanding, i feel stupid asking you this. But, i implemented this in our company and your hack has been working for 10 days now. Suddenly, our productions managers notes are gone.

We are using index + production order ID as unique ID. My questions goes, even tho the index changes from time to time, will that result in loss of comments? From my basic undestanding, now matter the index (even tho it changes from 5 to 7) the "new" unique ID is stored in that manual data query and is loading back into my main query..

Is that correct, or will change in index result in loss of comments?

However, the comments were there for days, but this morning all his were gone.

Thank you in advance.