You helped me solve a long-standing blocker of how make a power query output also serve as an input form. This was the only video that I am aware of that addresses this issue. Thanks!
The best and simplest solution for this problem I've seen so far! Thanks for sharing! I suggest that you change the title of this video because it's really hard to know the content for newbies.. If I had known several days ago that it has all the solutions for "additional columns that can be edited in excel power query table", I would have watched it much earlier. But you can also include it to the original title. Thanks again!
You are a life saver! Thank you so much!!!! I have spent the last several hours combing through Excel videos trying to find the fix and you did it in 6 minutes. I just hit the follow button :)
Great application! Thank you Doug for sharing this; I needed to create a dynamic forecast model for a client that would update based on new input and be referenced later. This model works perfectly.
Aw man, this is great! There isn't a lot of information on this but I read one website and watched two other videos and I just had issues. This was the most simple method that actually worked for me! Thank you! I'm using this so I can create a file directory that uses tags. Windows tagging system is limited to only certain files and I'm trying to do a sort of Inbox Zero / Roam Research / Mind Mapping version of storing files, but it needs tagging to work and this did it!
Hi Doug. Thank you for the video. Your technique works great when applying sorts. However, I'm trying to analyze what happens when I add a new row towards the top of my raw data (ex. adding a record in the 4th line when there's 10 lines of data). The power query doesn't pick up on the fact that a comment should be tied to a specific record in this case and subsequently, my comments shift and are no longer in the correct row. Do you have any suggestions on how to address this?
This helped tremendously. However, what if the "comment" column and additional columns had formulas in them? I'm finding that when refreshing the data, the formulas are overwritten with the raw data.
Add another column to the end of your green table. In the query area, check the box for that new column in the Merge step. Delete the extra column with the number after it.
Hey Doug, I am attempting to use a connection to a query, rather than a query itself (if I bring up Queries & Connections my reference is listed on the right-hand column under Connections)? How would this change things? Thanks! Love your videos.
Excellent idea!!! I applied it and this works. But, I have a different scenario. If is do all the steps as you mentioned, but with two tables combined and if I add new data(row) to one of the tables and refresh the power query table then the comment column values are changing, from the row where it got added. Can you please help me on this.
Hi Doug, Thanks for sharing; I tried this approach and works great; One question I found is that when I tried to change the connection name after establishing the merge, it detects table not found, so is it not possible to change connection name after the merge is set?
@@DougHExcel So I'll have to change the names in both the original table and connection I believe; Final question, have you tried adding another column to the table after the merge, say for example in addition to the first and last name, I want to insert another gender column in between the name and comment, can I just do that to the original source table and the update will get reflected? Thx!
i did the exact same steps and encounter this weird bug too 5:55, only thing to do is to hit refresh again and again, anyone know how to solve it? or is there another way?
Will this work with existing columns that exist in both the output table and source table? For example If I update First Name and Last Name column in output spreadsheet, will the table self reference itself and use that value instead of what exists on the source value?
How would you alter this example where the first table (blue) is a power query in itself? I am running a report for work, importing the data via power query then I want to add a column for comments.
Question, I really, REALLY NEED HELP. In the main table, if I change a value, it erases my manual input. How can I stop that? Would appreciate your help!
Hi Dough, really nice work, thank you. I have a problem with self referencing, when the source is growing. My source are some Data which is every night gettin bigger. After that growing i make a comment on each row every morning..the comments gettin messi every night after the new data is imported. do you have an idea`?
Putting a note/comment in a cell and copy/paste to that cell from another would overwrite it and that's normal behavior. Puttings a comment is in the adjacent cell like on the right like in this video is the other option.
Hi Dough. This is a great example. I tried it with my data. (Almost) everything works. If I sort the data in the left table and refresh the right table, it refreshes but the order is wrong. The right table is not in the same order as the left table. I then tried it with exactly your data. The order of the right table does not match the left table after an refresh. THIS PROBLEM ALSO OCCURS IN YOUR VIDEO. At 5:57 you can see that the table on the right is sorted differently than the table on the left. Am I the first to notice this, or is it not a bug at all and is there a simple explanation for the phenomenon.
Hi Roland, When you get to the Merge step, before you click "Close and load", sort by the ID and then click close and load. The results should then be sorted correctly.
Hi Dough, I have a table connected to an external SQL DB . I need to comment on some of the entries and I've followed the steps .. what i find is that it works fine , until I refresh the source data . Do you have a suggestion ?
there could be some artifacts that mess it up. Suggest to source the SQL DB to a table that you eventually want the columns set up in the way you want and doing any additional ETL. That becomes the bridge table that you can reference to have the output the self references. Hope that works...
I have a scenario where I am using a combined customer lookup table month after month in a process that I would like to mostly automate using power query. The lookup table is used at the beginning of my process and then any new customers that need to be combined (determined through manual process and updating) get added to the lookup table to be used in the next month. The process in your video almost gets me there, except for the column that I am updating already exists in my original table. Any thoughts on how to make this work?
This is almost a database type of scenario where you're updating a field based on the latest. Maybe a date field that indicates when last updated so you can compare and take the latest?
This is such a confusing thing (not your video, just the whole process). But genius, worked exactly as I intended, just hopefully it can work with huge data sets (4000 rows, etc)
Useful, but can you explain why the sort order on your output (green) Table is not the same as the sort order on the input table (blue) when you refresh after adding the self-referencing table ? I agree that the comment has “stuck” to the correct record, but the sort order is weird when you first refresh. You refresh again and even though the blue source table has not changed between the first and second refresh the sort order of the output table changes again, but still does not match the source table ? Confused ! After adding sushi comment you re-sort the input table to 1,2,3,4,5 but when you first refresh the output table you first get 1,4,2,3,5 then you refresh again and get 1,2,4,3,5. -seems a bit random ?
this is very helpful, what to do if my data has not unique ID? i tried to create a unique ID by creating a column in PQ by concatenating 3 columns, looks ok, but when i complete the self refer, every time i reload data, somehow i am loading more and more rows, a couple refreshes turnes 2500 rows to over half million
I’m going to give this a try. I need to build an Excel file that will have about 12k rows. It will be used by about 20 people. They will be entering values in a forecast column. Anyone see any issues with this working while several people are working in the same file at the same time?
Doesn't work for me despite several permutations within the PQ environment- Instead create a new Excel table with the columns from your PQ Table and your NEW desired Columns, then upon a refresh of your PQ table, update your Excel Table for the new entries (records) via a simple copy and paste of values only. In other words, a Manual Refresh 😆 to your Excel Table. Conditional formatting helps identify new PQ entries . No law a PQ Table is required. Only need a suitable Table to my needs! PQ does the heavy lifting, and I cross the T's and dot the I's 🤓
You helped me solve a long-standing blocker of how make a power query output also serve as an input form. This was the only video that I am aware of that addresses this issue. Thanks!
Glad it helped!
This is going to be a game changer for me. I’ve been trying to figure out how to do this. Thank you very much.
You're most welcome!
The best and simplest solution for this problem I've seen so far! Thanks for sharing! I suggest that you change the title of this video because it's really hard to know the content for newbies.. If I had known several days ago that it has all the solutions for "additional columns that can be edited in excel power query table", I would have watched it much earlier. But you can also include it to the original title. Thanks again!
Thanks for the idea!
Exactly what I’d been looking for! Thanks so much!
Great to hear!
100 Thumbs up for the Thumb Nail, but 101 for the content of the video, Doug : )
Thanks Mike!
You are a life saver! Thank you so much!!!! I have spent the last several hours combing through Excel videos trying to find the fix and you did it in 6 minutes. I just hit the follow button :)
Glad it helped!
Brilliant! Thank you so much. It took alot of searching to find a solution to this. I'm so glad I stumbled upon your video.
Hi ReginaKing WorkEmail, glad it helped! Thanks for watching and commenting!
Thank you for this video. You are going to mark me look so good in my new job role.
Excellent solution to this issue. Thanks for sharing 👍
Rarely comment, but you are a life saver bro!!
Glad it helped!
Great application! Thank you Doug for sharing this; I needed to create a dynamic forecast model for a client that would update based on new input and be referenced later.
This model works perfectly.
Glad it was helpful!
GREAT video. Very helpful. What happens if I need to add another column after I've already self referenced? Do I have to start over?
Thanks for this tip, I know I'll be using it very soon with a number of projects I'm currently working on.
Thanks Michael Jones, glad it'll help!
Aw man, this is great! There isn't a lot of information on this but I read one website and watched two other videos and I just had issues. This was the most simple method that actually worked for me! Thank you!
I'm using this so I can create a file directory that uses tags. Windows tagging system is limited to only certain files and I'm trying to do a sort of Inbox Zero / Roam Research / Mind Mapping version of storing files, but it needs tagging to work and this did it!
Awesome, glad it worked for you!
Wish I could like this more than once my guy!
Hi the name is hazelnt, thanks for the kind words!
Already know how I’m going to apply this. Thank you!
Hi David Andrews...you're welcome, glad you liked!
Good job man, finally I found the method
Thanks for the comment!
Just what I have been looking for.... for a lookng time. Thanks.
Hi Lars Hansson, glad you liked it, thanks for commenting!
Brilliant and elegant !!
Hi Philippe Julien, thanks for the comment!
Hi Doug. Thank you for the video. Your technique works great when applying sorts. However, I'm trying to analyze what happens when I add a new row towards the top of my raw data (ex. adding a record in the 4th line when there's 10 lines of data). The power query doesn't pick up on the fact that a comment should be tied to a specific record in this case and subsequently, my comments shift and are no longer in the correct row. Do you have any suggestions on how to address this?
Interesting, I'll see if I can test this and maybe it'll be a future video...
This is the same kind of thing I'm looking for too. The source list is updated often (new records or some cells in a record change).
I have the same issue!
Same issue here lol
Same issue here..
Amazing Doug! Thanks for sharing! ❤️ You earned a new subscriber. ☺️
Thanks for the sub!
This helped tremendously. However, what if the "comment" column and additional columns had formulas in them? I'm finding that when refreshing the data, the formulas are overwritten with the raw data.
Hey Doug. Just found this and this is what I need but do you know how to load as Connection for MAC? it seems to be missing this function
That a great content, thanks Doug !
You’re very welcome!
Your video was very helpful
Glad it was helpful!
Hi! Very helpfull! How would you add another column afterwards without redoing the whole exercise?
Add another column to the end of your green table. In the query area, check the box for that new column in the Merge step. Delete the extra column with the number after it.
You are a genius 🙏🏽
Thanks!
Hey Doug,
I am attempting to use a connection to a query, rather than a query itself (if I bring up Queries & Connections my reference is listed on the right-hand column under Connections)? How would this change things?
Thanks! Love your videos.
Excellent idea!!! I applied it and this works. But, I have a different scenario. If is do all the steps as you mentioned, but with two tables combined and if I add new data(row) to one of the tables and refresh the power query table then the comment column values are changing, from the row where it got added. Can you please help me on this.
Hi Doug, Thanks for sharing; I tried this approach and works great; One question I found is that when I tried to change the connection name after establishing the merge, it detects table not found, so is it not possible to change connection name after the merge is set?
if changing name and it's a reference in the steps, you'd need to change that to stay consistent
@@DougHExcel So I'll have to change the names in both the original table and connection I believe; Final question, have you tried adding another column to the table after the merge, say for example in addition to the first and last name, I want to insert another gender column in between the name and comment, can I just do that to the original source table and the update will get reflected? Thx!
i did the exact same steps and encounter this weird bug too 5:55, only thing to do is to hit refresh again and again, anyone know how to solve it? or is there another way?
Very usefull ! Thanks for sharing that :)
Reminds me why I’m always eagerly waiting for your next video! :)
Hi George Tosounidis, glad you liked it, thanks for commenting!
is this possible to making data entry form wihout vba and can be share with excel 365 with this method?
very useful!
Glad to hear that!
Really cool presentation
Hi Zeng Yonge, thanks for the comment!
Will this work with existing columns that exist in both the output table and source table? For example If I update First Name and Last Name column in output spreadsheet, will the table self reference itself and use that value instead of what exists on the source value?
How would you alter this example where the first table (blue) is a power query in itself? I am running a report for work, importing the data via power query then I want to add a column for comments.
Very helpful, thank you :)
Thanks Hendrick McDonald, glad it helped!
Question, I really, REALLY NEED HELP. In the main table, if I change a value, it erases my manual input. How can I stop that? Would appreciate your help!
awesome, thank you!
You're welcome!
A nice tricks of PowerQuery Nice
Hi Mohamed Chakroun, thanks for the comment!
Hi Dough, really nice work, thank you. I have a problem with self referencing, when the source is growing. My source are some Data which is every night gettin bigger. After that growing i make a comment on each row every morning..the comments gettin messi every night after the new data is imported. do you have an idea`?
Putting a note/comment in a cell and copy/paste to that cell from another would overwrite it and that's normal behavior. Puttings a comment is in the adjacent cell like on the right like in this video is the other option.
Hi Dough. This is a great example. I tried it with my data. (Almost) everything works. If I sort the data in the left table and refresh the right table, it refreshes but the order is wrong. The right table is not in the same order as the left table. I then tried it with exactly your data. The order of the right table does not match the left table after an refresh. THIS PROBLEM ALSO OCCURS IN YOUR VIDEO. At 5:57 you can see that the table on the right is sorted differently than the table on the left. Am I the first to notice this, or is it not a bug at all and is there a simple explanation for the phenomenon.
Hi Roland, When you get to the Merge step, before you click "Close and load", sort by the ID and then click close and load. The results should then be sorted correctly.
Thanks for this - I’m having an issue where after I delete the comments 2 column after refreshing the data it returns ? Any ideas ?
This is exactly what I was looking for, however i keep getting the duplicate columns come back after i delete them, not sure how to fix that
yeah, same is happening for me
Hi Dough,
I have a table connected to an external SQL DB . I need to comment on some of the entries and I've followed the steps .. what i find is that it works fine , until I refresh the source data . Do you have a suggestion ?
I can't thank you enough for the tutorial !!!
there could be some artifacts that mess it up. Suggest to source the SQL DB to a table that you eventually want the columns set up in the way you want and doing any additional ETL. That becomes the bridge table that you can reference to have the output the self references. Hope that works...
Good one👍
Hi santosh subudhi, thanks for the comment!
I have a scenario where I am using a combined customer lookup table month after month in a process that I would like to mostly automate using power query. The lookup table is used at the beginning of my process and then any new customers that need to be combined (determined through manual process and updating) get added to the lookup table to be used in the next month. The process in your video almost gets me there, except for the column that I am updating already exists in my original table. Any thoughts on how to make this work?
This is almost a database type of scenario where you're updating a field based on the latest. Maybe a date field that indicates when last updated so you can compare and take the latest?
This is not working when extra line is coming in de data...
Is there a way to do this on access?
Hi Abstract Life, thanks for the comment! Sorry don't know Access 😐
I cannot get this to work at all. Any user added data stays in the same row it was added to when the data is refreshed.
This is such a confusing thing (not your video, just the whole process). But genius, worked exactly as I intended, just hopefully it can work with huge data sets (4000 rows, etc)
Glad it helped!
@@DougHExcel The only issue I ran into using this is when I also needed some form of dynamic headers.. it wouldn't work with this :(
Useful, but can you explain why the sort order on your output (green) Table is not the same as the sort order on the input table (blue) when you refresh after adding the self-referencing table ?
I agree that the comment has “stuck” to the correct record, but the sort order is weird when you first refresh. You refresh again and even though the blue source table has not changed between the first and second refresh the sort order of the output table changes again, but still does not match the source table ? Confused !
After adding sushi comment you re-sort the input table to 1,2,3,4,5 but when you first refresh the output table you first get 1,4,2,3,5 then you refresh again and get 1,2,4,3,5. -seems a bit random ?
I notice it rearranges the order in the merge step, but haven't dug into to deep on what criteria it uses for the sort there...
this is very helpful, what to do if my data has not unique ID? i tried to create a unique ID by creating a column in PQ by concatenating 3 columns, looks ok, but when i complete the self refer, every time i reload data, somehow i am loading more and more rows, a couple refreshes turnes 2500 rows to over half million
found my unique ID wasn't so unique. fixed that and now it works, thanks!
I’m going to give this a try. I need to build an Excel file that will have about 12k rows. It will be used by about 20 people. They will be entering values in a forecast column. Anyone see any issues with this working while several people are working in the same file at the same time?
Doesn't work for me despite several permutations within the PQ environment- Instead create a new Excel table with the columns from your PQ Table and your NEW desired Columns, then upon a refresh of your PQ table, update your Excel Table for the new entries (records) via a simple copy and paste of values only. In other words, a Manual Refresh 😆 to your Excel Table. Conditional formatting helps identify new PQ entries . No law a PQ Table is required. Only need a suitable Table to my needs! PQ does the heavy lifting, and I cross the T's and dot the I's 🤓