Forum Discussion
User input fields in power query
HelloDan_TWE,
Power Query cannot preserve manual user input in columns added to a table it loads to Excel. When the query refreshes, Power Query recreates the output table and any values typed by users are overwritten.
The recommended approach is to keep the Power Query output and the user input in separate tables and then combine them.
Load the Power Query result (for example First Name and Last Name) to an Excel table.
Create a second Excel table for user input that contains the same key columns (First Name and Last Name) plus the additional fields users will complete.
In Power Query merge the query table with the user input table using the key columns.
Expand the user input columns and load the merged result.
This allows the employee list to refresh from Power Query while preserving user-entered values in the separate input table.
Microsoft documentation
https://learn.microsoft.com/power-query/merge-queries-overview
HelloDan_TWE,
Power Query cannot preserve manual user input in columns added to a table it loads to Excel. When the query refreshes, Power Query recreates the output table and any values typed by users are overwritten.
The recommended approach is to keep the Power Query output and the user input in separate tables and then combine them.
Load the Power Query result (for example First Name and Last Name) to an Excel table.
Create a second Excel table for user input that contains the same key columns (First Name and Last Name) plus the additional fields users will complete.
In Power Query merge the query table with the user input table using the key columns.
Expand the user input columns and load the merged result.
This allows the employee list to refresh from Power Query while preserving user-entered values in the separate input table.
Microsoft documentation
https://learn.microsoft.com/power-query/merge-queries-overview
That is how I managed to do it the first time. I thought there might be some Excel M whiz-bang {something} I didn't know about. LOL! Thanks for confirming. You're the best.
I was pleasantly surprised when power query automatically moved the user fields table over as needed on the sheet it was displayed, since the two tables are butted up right next to each other. One of the few times Microsoft surprised me in a good way. LOL!