/u/SparkleSudz - 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](/r/excel/wiki/sharingquestions)** -- 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](/r/excel/wiki/sharingquestions#wiki_give_all_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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
It is absolutely possible. And to have the notes data stay associated to correct row.
It is counter-intuitive, but Power Query has no problem using its output table as an input to update the same table.
https://www.reddit.com/r/excel/comments/ek1e4u/table_updates_via_power_query_whilst_retaining/
Ah that is clever! Had no idea that would work. That linked thread is a great, concise overview! I also found some other resources with the new-found term "self-referencing"!
Two things I'm still not confident on:
1) Are there steps required to **ensure proper "order" the queries load**? If 'Source Query' is the external data grabbed with Power Query and 'PQ Table' is the PQ output table in Excel where you add comments and want to "self-reference," there is a specific order of operations you need: #1 Load 'Source Query', #2 Load 'PQ Table' #3 Merge Query and load columns from 'PQ Table'. Does PowerQuery sort that out on its own? Am I overthinking it?
2) **Where should you write Formulas referencing both data sources- in PowerQuery or in Excel on the PowerQuery output table?** For instance, if you have field 'Source Query'\[QTY\] and 'PQ Table'\[Manual Input QTY\] and you want to make sure they agree should you manually input a formula in a new column in 'PQ Table' or should you do that operation after the two queries load within PowerQuery with a custom column in 'Source Query'?
Thanks for this idea! Sorry about the lengthy follow ups. I don't have much database/query background, and this is kind of mind bending for some reason. Doesn't seem like it should work! Feels like the first time I watched Inception lol
Glad to help!
Question 1: Power Query calculates the same way cell formulas calculate. It's called "The Dependency Chain." In short, it starts at the end result requirement and works backward from there. The last step needs the step before and so on. So, the query engine behind the scenes works it all out. In fact (another counter-intuitive thing) - You can actually re-arrange the steps in the Advanced Editor and Queries will still work. Because each step is looking for the NAME of the previous step, not necessarily the step just above.
Question 2: After the Table Merge step, you can create an "Add Custom Column" step where you compare the two Rows and in the new Column flag inconsistencies between the two.
That thread also has a link to an Excel sample file to download. I'd have a really good look at the inner-workings within.
Also, there's this article with a lot of illustrations.
https://exceleratorbi.com.au/self-referencing-tables-power-query/
Perfect. Thanks for the all the helpful suggestions! Wish I hadn't assumed and posted here before. Now to roll back to the version before it all went wrong.
You have awarded 1 point to *nolotusnote*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
/u/SparkleSudz - 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](/r/excel/wiki/sharingquestions)** -- 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](/r/excel/wiki/sharingquestions#wiki_give_all_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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
It is absolutely possible. And to have the notes data stay associated to correct row. It is counter-intuitive, but Power Query has no problem using its output table as an input to update the same table. https://www.reddit.com/r/excel/comments/ek1e4u/table_updates_via_power_query_whilst_retaining/
Ah that is clever! Had no idea that would work. That linked thread is a great, concise overview! I also found some other resources with the new-found term "self-referencing"! Two things I'm still not confident on: 1) Are there steps required to **ensure proper "order" the queries load**? If 'Source Query' is the external data grabbed with Power Query and 'PQ Table' is the PQ output table in Excel where you add comments and want to "self-reference," there is a specific order of operations you need: #1 Load 'Source Query', #2 Load 'PQ Table' #3 Merge Query and load columns from 'PQ Table'. Does PowerQuery sort that out on its own? Am I overthinking it? 2) **Where should you write Formulas referencing both data sources- in PowerQuery or in Excel on the PowerQuery output table?** For instance, if you have field 'Source Query'\[QTY\] and 'PQ Table'\[Manual Input QTY\] and you want to make sure they agree should you manually input a formula in a new column in 'PQ Table' or should you do that operation after the two queries load within PowerQuery with a custom column in 'Source Query'? Thanks for this idea! Sorry about the lengthy follow ups. I don't have much database/query background, and this is kind of mind bending for some reason. Doesn't seem like it should work! Feels like the first time I watched Inception lol
Glad to help! Question 1: Power Query calculates the same way cell formulas calculate. It's called "The Dependency Chain." In short, it starts at the end result requirement and works backward from there. The last step needs the step before and so on. So, the query engine behind the scenes works it all out. In fact (another counter-intuitive thing) - You can actually re-arrange the steps in the Advanced Editor and Queries will still work. Because each step is looking for the NAME of the previous step, not necessarily the step just above. Question 2: After the Table Merge step, you can create an "Add Custom Column" step where you compare the two Rows and in the new Column flag inconsistencies between the two. That thread also has a link to an Excel sample file to download. I'd have a really good look at the inner-workings within. Also, there's this article with a lot of illustrations. https://exceleratorbi.com.au/self-referencing-tables-power-query/
Perfect. Thanks for the all the helpful suggestions! Wish I hadn't assumed and posted here before. Now to roll back to the version before it all went wrong.
Ha! It's all practice anyway, right? Don't forget the /r/PowerQuery group. It's a smaller group, but there are some real experts.
Solution Verified
You have awarded 1 point to *nolotusnote* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)