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.
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.
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.
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!
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.
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?
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.
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.
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.
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)
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...
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.
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 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?
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 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
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 🤓
![CHILE vs. BRASIL [1-2] | RESUMEN | ELIMINATORIAS SUDAMERICANAS | FECHA 9](http://i.ytimg.com/vi/5ObJt_71AYc/mqdefault.jpg)
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!
100 Thumbs up for the Thumb Nail, but 101 for the content of the video, Doug : )
Thanks Mike!
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!
Thank you for this video. You are going to mark me look so good in my new job role.
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!
Excellent solution to this issue. Thanks for sharing 👍
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!
Just what I have been looking for.... for a lookng time. Thanks.
Hi Lars Hansson, glad you liked it, thanks for commenting!
Amazing Doug! Thanks for sharing! ❤️ You earned a new subscriber. ☺️
Thanks for the sub!
Rarely comment, but you are a life saver bro!!
Glad it helped!
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!
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!
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?
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!
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.
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!
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.
Good job man, finally I found the method
Thanks for the comment!
Brilliant and elegant !!
Hi Philippe Julien, thanks for the comment!
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!
That a great content, thanks Doug !
You’re very welcome!
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.
Your video was very helpful
Glad it was helpful!
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!
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..
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.
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
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.
Really cool presentation
Hi Zeng Yonge, thanks for the comment!
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!
You are a genius 🙏🏽
Thanks!
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?
awesome, thank you!
You're welcome!
Very helpful, thank you :)
Thanks Hendrick McDonald, glad it helped!
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
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.
A nice tricks of PowerQuery Nice
Hi Mohamed Chakroun, thanks for the comment!
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 :(
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...
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.
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...
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?
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?
Good one👍
Hi santosh subudhi, thanks for the comment!
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 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 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 😐
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 🤓